Structured Query Language Fundamentals and Aggregation Questions

This topic covers core Structured Query Language fundamentals for analytical querying and reporting. Candidates should be able to write correct, readable, and maintainable SELECT queries with filtering using WHERE, sorting with ORDER BY, grouping with GROUP BY, and group filtering with HAVING. They should apply aggregate functions such as COUNT, COUNT DISTINCT, SUM, AVG, MIN, and MAX and understand how NULL values affect results, how empty result sets behave, and when to use different counting approaches. The scope includes date and time filtering, basic cohort segmentation, and common time based comparisons used to compute metrics such as daily active users, average revenue per user, and period over period comparisons. Candidates are expected to use basic joins and join predicates including inner joins and left joins, write simple subqueries and conditional expressions, and perform common data transformation and cleansing patterns to prepare data for analysis. Finally, this topic assesses query readability and maintainability practices such as aliasing and formatting, plus awareness of elementary performance considerations including index usage and avoiding unnecessary full table scans for entry to mid level analytical tasks.

HardTechnical

44 practiced

A GROUP BY on customer_id creates large temporary aggregates and memory pressure. Propose SQL and system strategies to mitigate this: use approximate aggregations, heavy-hitter pre-aggregation, sampling, or external sketch algorithms. Provide example SQL using APPROX_COUNT_DISTINCT or top-k pre-aggregation pattern.

Sample Answer

Approach: reduce per-customer state by trading exactness for memory and by isolating heavy hitters. Combine approximate distinct-counts, top-k pre-aggregation, and sampling/sketches plus system-level mitigations (partitioning, spill-to-disk, materialized pre-agg). Below are concrete patterns and trade-offs.

1) APPROXIMATE distinct counts (HyperLogLog / APPROX_COUNT_DISTINCT)- fast, low-memory, acceptable error for many analytics:

sql

SELECT
  customer_id,
  APPROX_COUNT_DISTINCT(event_id) AS approx_events
FROM events
WHERE event_ts BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY customer_id;

Trade-off: small relative error; not suitable when exact per-customer counts are required.

2) Heavy-hitter (top-k) pre-aggregation + approximate tail- First find top customers using sample or streaming sketch, fully aggregate them exactly; approximate the rest.

sql

-- Step A: find top-k customers (approx)
WITH top_customers AS (
  SELECT customer_id
  FROM events
  GROUP BY customer_id
  ORDER BY COUNT(*) DESC
  LIMIT 1000
)
-- Step B: exact for top-k, approx for others
SELECT
  customer_id,
  SUM(cnt) AS events_exact
FROM (
  SELECT customer_id, COUNT(*) AS cnt
  FROM events
  WHERE customer_id IN (SELECT customer_id FROM top_customers)
  GROUP BY customer_id
  UNION ALL
  SELECT 'TAIL' AS customer_id, APPROX_COUNT_DISTINCT(event_id) AS cnt
  FROM events
  WHERE customer_id NOT IN (SELECT customer_id FROM top_customers)
) t
GROUP BY customer_id;

Trade-off: accurate for top customers; aggregated tail is coarse.

3) Sampling + sketches- Use uniform sampling (e.g., WHERE rand() < 0.01) for exploratory queries; scale up with inverse-prob weighting.- Use external sketches (HLL, Count-Min) in ingestion (pre-aggregated in OLAP tables) to avoid large GROUP BY at query time.

System strategies:- Partition and hash-distribute on customer_id so reduce-phase is balanced.- Increase sort/spill buffers, enable disk spilling and distributed aggregation.- Maintain incremental/materialized views or streaming pre-aggregations (Kafka/KSQ, Flink) to keep per-customer state incremental.

When to pick each:- Use APPROX_COUNT_DISTINCT/HLL for high-cardinality distinct counts.- Use top-k pre-agg when a small fraction of customers drive most metrics.- Use sampling/sketches for quick exploratory analytics.- Combine with system-level partitioning, spilling, and incremental pre-aggregation for production reliability.

MediumTechnical

56 practiced

You find emails in users table contain leading/trailing whitespace and inconsistent casing. Provide a SQL transformation to standardize emails (TRIM, LOWER), remove obviously invalid emails without '@', and deduplicate users keeping the most recent signup_date. Describe edge cases and how to safely run this in production.

Sample Answer

Approach: normalize email strings with TRIM and LOWER, filter out clearly invalid addresses (no '@' or NULL), then deduplicate by keeping the row with the latest signup_date per normalized email. Do this in a staging table first, validate, then swap or update production.

SQL (Postgres example):

sql

-- 1. Create a staging table with normalized emails and filter obvious invalids
CREATE TABLE users_cleaned AS
SELECT
  id,
  LOWER(TRIM(email)) AS email_norm,
  name,
  signup_date,
  other_columns...
FROM users
WHERE email IS NOT NULL
  AND position('@' IN email) > 1  -- simple check: contains '@' and not first char
;

-- 2. Deduplicate: keep latest signup_date per normalized email
CREATE TABLE users_deduped AS
SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY email_norm ORDER BY signup_date DESC, id DESC) AS rn
  FROM users_cleaned
) t
WHERE rn = 1;

In-place alternative (transactional):

sql

BEGIN;

-- add normalized column
ALTER TABLE users ADD COLUMN email_norm text;
UPDATE users SET email_norm = LOWER(TRIM(email));

-- delete obvious invalids
DELETE FROM users WHERE email_norm IS NULL OR position('@' IN email_norm) = 0;

-- delete duplicates keeping latest signup_date
DELETE FROM users u
USING (
  SELECT id
  FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email_norm ORDER BY signup_date DESC, id DESC) AS rn
    FROM users
  ) x WHERE rn > 1
) dup
WHERE u.id = dup.id;

COMMIT;

Key points / reasoning:- TRIM removes whitespace; LOWER standardizes casing for comparisons (local-part is technically case-sensitive but almost always treated case-insensitively in practice).- ROW_NUMBER guarantees one canonical row per email_norm (ties broken by signup_date, then id).- Filtering with position('@' IN ...) is a lightweight filter; full RFC validation is complex — consider regex or third-party validation if needed.

Edge cases:- Legitimately case-sensitive local-parts (rare) — lowering may merge distinct accounts.- Sub-addressing (alice+tag@example.com) — may want to strip +tags depending on business rules.- Internationalized emails, Unicode, hidden characters — may need normalization (NFC) and removal of zero-width spaces.- Multiple '@' or malformed domains — simple filter may not catch; use regex: email ~* '^[^@\s]+@[^@\s]+\.[^@\s]+$' for stricter validation.- Nulls and empty strings — handle explicitly.

Safe production run:- Run on a copy or staging dataset first; validate counts and sample rows.- Wrap destructive ops in transactions and take backups (EXPORT or CREATE TABLE users_backup AS SELECT * FROM users).- Log changes (audit table) to map old_id → kept_id when merging users.- Run during low-traffic window; ensure referential integrity (update foreign keys pointing to deleted ids or use soft-delete / mapping).- Add tests and constraints after: e.g., UNIQUE(email_norm) with NULLs allowed, and indexes for performance.- Communicate to stakeholders and have rollback plan.

This workflow balances safety, simplicity, and explainability; adapt stricter validation and normalization rules based on product requirements.

EasyTechnical

56 practiced

Given users(user_id INT, signup_date DATE) and orders(order_id INT, user_id INT, amount DECIMAL, order_date DATE), write a query to list users who have placed at least one order in 2024. Use a subquery (IN or EXISTS) rather than a JOIN, and describe performance trade-offs between subquery and join in this simple membership case.

Sample Answer

Approach: use a semi-join via a subquery (EXISTS or IN) to return users who placed at least one order in 2024. This avoids returning duplicates from orders and expresses membership clearly.

EXISTS version (recommended):

sql

SELECT u.user_id, u.signup_date
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.user_id
    AND o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
);

IN version:

sql

SELECT user_id, signup_date
FROM users
WHERE user_id IN (
  SELECT DISTINCT user_id
  FROM orders
  WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
);

Key points & trade-offs:- Semantically both return the same membership set. EXISTS checks per user if any qualifying order exists; IN checks membership against a set of user_ids.- Performance: modern RDBMS optimize both into a semi-join. EXISTS often performs better for correlated checks because it can short-circuit on the first match; IN may require building/reading the subquery result (or hash set) especially if DISTINCT is needed.- Indexes matter more: an index on orders(user_id, order_date) lets the subquery/scan be very fast. Without indexes, both will do more I/O.- JOIN alternative (INNER JOIN orders) can produce duplicate user rows when a user has multiple orders — you'd need DISTINCT or GROUP BY to dedupe, adding cost.- NULLs: IN behaves differently if subquery can return NULLs; EXISTS has no NULL pitfalls.

Complexity: roughly O(#users * cost_to_find_orders_per_user) but with proper indexing it's near linear and efficient.

MediumTechnical

56 practiced

You have a large events table with an index on event_time. You need top 10 event_types by count over the last month. Which is better for performance: filtering in WHERE before GROUP BY or filtering aggregated groups with HAVING? Write the efficient SQL and explain reasoning about predicate pushdown and index usage.

Sample Answer

Filtering in WHERE before GROUP BY is almost always better: it lets the database apply the predicate early (predicate pushdown) and use the event_time index to only read rows from the last month, reducing I/O and work for the GROUP BY. HAVING runs after aggregation, so the engine must aggregate over a much larger set (possibly the whole table) and then discard groups — which is expensive.

Efficient SQL (Postgres/MySQL-style):

sql

-- Use WHERE to limit rows first, then aggregate and sort
SELECT event_type, COUNT(*) AS cnt
FROM events
WHERE event_time >= now() - interval '1 month'
GROUP BY event_type
ORDER BY cnt DESC
LIMIT 10;

Key reasoning:- Predicate pushdown: WHERE on event_time can be applied during index scan to limit scanned rows.- Index usage: with an index on event_time the planner can do an index range scan for the last month; if (event_time, event_type) is a covering index, the engine may satisfy grouping without touching the table.- HAVING version (moving the time filter into HAVING) forces aggregation of many rows first, blocking early pruning and often preventing use of index range scans.

Additional optimizations:- Create a composite index (event_time, event_type) or a covering index (event_type, event_time) depending on DB and cardinality.- Consider partitioning by time for very large tables so the planner prunes partitions.

HardTechnical

46 practiced

Compare date_trunc/month truncation and timezone handling across Postgres, BigQuery, and Snowflake when grouping by month. Provide example SQL for truncating to month and highlight pitfalls when dates are stored as strings. Recommend robust patterns to minimize cross-engine differences.

Sample Answer

When grouping by month across Postgres, BigQuery, and Snowflake you must handle two axes: truncation function semantics (date vs timestamp) and timezone interpretation (stored timezone or UTC). Below are concise comparisons, example SQL, pitfalls for string dates, and robust patterns.

Postgres- Use date_trunc('month', timestamp) — returns timestamp with same TZ info.- If column is timestamptz, date_trunc respects UTC/localization of value.

sql

SELECT date_trunc('month', created_at AT TIME ZONE 'UTC') AS month_utc, COUNT(*)
FROM events
GROUP BY 1;

BigQuery- Use DATETIME_TRUNC / TIMESTAMP_TRUNC. TIMESTAMP_TRUNC requires a timezone argument for truncation to calendar boundaries.

sql

SELECT TIMESTAMP_TRUNC(TIMESTAMP(created_at), MONTH, 'UTC') AS month_utc, COUNT(*) FROM events;

Snowflake- Use DATE_TRUNC('MONTH', <expr>) — works on DATE/TIMESTAMP; defaults to session timezone for TIMESTAMP_NTZ/LOCAL handling.

sql

SELECT DATE_TRUNC('MONTH', CONVERT_TIMEZONE('UTC', created_at)) AS month_utc, COUNT(*) FROM events;

Pitfalls when dates stored as strings- Implicit casts vary: '2025-01-31T23:00:00Z' may parse differently. Engines may assume session TZ or no TZ.- Losing timezone: casting to DATE drops time zone and can shift month across zones (e.g., UTC→-8 causes previous day/month).

Robust patterns1. Normalize on ingest: store timestamps as canonical TIMESTAMP WITH TIME ZONE (or Unix epoch) and store timezone metadata.2. Explicit parsing: always parse strings with explicit format and timezone. - Postgres: to_timestamp(..., 'YYYY-MM-DD"T"HH24:MI:SSOF') - BigQuery: PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%E*S%Ez', created_at) - Snowflake: TO_TIMESTAMP_TZ(created_at, 'YYYY-MM-DD"T"HH24:MI:SSFF3TZHTZM')3. Truncate in UTC (or a single agreed timezone) before grouping: convert to UTC then date_trunc/TIMESTAMP_TRUNC/DATE_TRUNC.4. Use dates (no time) only when business logic is date-local; otherwise use month boundaries computed in target timezone: e.g., TIMESTAMP_TRUNC(ts, MONTH, 'America/Los_Angeles').5. Write adapter-layer SQL functions (or views) to centralize parsing & truncation to reduce engine differences.

Time/space note: test edge cases around DST and month boundaries around midnight; include unit tests with representative timestamps.

Unlock Full Question Bank

Get access to hundreds of Structured Query Language Fundamentals and Aggregation interview questions and detailed answers.

Join thousands of developers preparing for their dream job.