SQL Practice
Hands-on SQL for analysts — aggregation, grouping, and the HAVING filter.
15 questions
JuniorTheoryVery commonINNER, LEFT, and FULL OUTER JOIN — what does each return for rows that have no match?
INNER, LEFT, and FULL OUTER JOIN — what does each return for rows that have no match?
INNER JOIN keeps only rows matching on both sides. LEFT JOIN keeps every left row and fills unmatched right columns with NULL. FULL OUTER JOIN keeps unmatched rows from both sides, padding with NULL. Only INNER drops non-matching rows entirely.
Common mistakes
- ✗Thinking LEFT JOIN can drop left rows when the right side has no match
- ✗Assuming INNER JOIN pads non-matching rows with NULL instead of dropping them
- ✗Confusing which side FULL OUTER preserves
Follow-up questions
- →How does adding a WHERE on the right table change a LEFT JOIN?
- →When would you pick FULL OUTER over a UNION of two LEFT JOINs?
MiddleCodeVery commonDeduplicate a table keeping one row per user — the latest by updated_at
Deduplicate a table keeping one row per user — the latest by updated_at
Rank rows within each user and keep the top one: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn, then filter rn = 1. DISTINCT cannot pick the latest; a plain GROUP BY user_id forces an aggregate on every other column.
Common mistakes
- ✗Expecting DISTINCT to pick the latest row per user
- ✗Assuming MAX() drags the other columns from the same row
- ✗Grouping by user_id but selecting ungrouped columns
Follow-up questions
- →How would you break ties on equal updated_at values?
- →When is DISTINCT ON simpler than ROW_NUMBER here?
MiddleDebuggingVery commonA LEFT JOIN silently acts like an INNER JOIN — a right-table filter is in WHERE. Fix it.
A LEFT JOIN silently acts like an INNER JOIN — a right-table filter is in WHERE. Fix it.
The WHERE o.status = 'completed' runs after the join and drops the NULL-padded unmatched rows, so the LEFT JOIN collapses to an INNER JOIN. Move the right-table predicate into the join: LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'completed'.
Common mistakes
- ✗Filtering the right table in WHERE after a LEFT JOIN
- ✗Blaming the LEFT JOIN keyword instead of the WHERE timing
- ✗Thinking ON may reference only the join-key columns
Follow-up questions
- →When is a right-table predicate in WHERE actually correct?
- →How does an IS NULL check in WHERE differ from moving it to ON?
JuniorTheoryCommonCOUNT(*), COUNT(col) and COUNT(DISTINCT col) on a nullable column — why three numbers?
COUNT(*), COUNT(col) and COUNT(DISTINCT col) on a nullable column — why three numbers?
COUNT(*) counts every row, NULLs included. COUNT(col) counts only rows where col is not NULL. COUNT(DISTINCT col) counts distinct non-NULL values. So on a nullable column the three differ — NULLs lift only COUNT(*), and duplicates lift only COUNT(col).
Common mistakes
- ✗Thinking COUNT(col) counts NULL rows like COUNT(*)
- ✗Assuming COUNT(DISTINCT col) keeps a NULL bucket
- ✗Believing the three counts always agree
Follow-up questions
- →How would you count the NULLs in the column explicitly?
- →When does COUNT(DISTINCT col) equal COUNT(col)?
JuniorTheoryCommonTo find clients who made more than one order on some day last month, where does the count filter go?
To find clients who made more than one order on some day last month, where does the count filter go?
The aggregate filter belongs in HAVING, not WHERE — WHERE cannot reference COUNT(). You must group by both client and day, then keep groups with more than one order: GROUP BY customer_id, order_date HAVING COUNT(order_id) > 1, with the date-range condition in WHERE.
Common mistakes
- ✗Putting COUNT() in WHERE instead of HAVING
- ✗Grouping by client only, missing the per-day requirement
- ✗Treating a growing order id as an order count
Follow-up questions
- →Why can't WHERE reference an aggregate like COUNT()?
- →What does grouping by both client and day change?
JuniorCodeCommonList employees earning the maximum salary
List employees earning the maximum salary
Compute the maximum salary once and select everyone matching it. A CTE makes it clean: WITH m AS (SELECT MAX(salary) s FROM employees) SELECT first_name FROM employees JOIN m ON salary = m.s. Equivalently WHERE salary = (SELECT MAX(salary) FROM employees).
Common mistakes
- ✗Mixing MAX() with a plain column without GROUP BY
- ✗Using LIMIT 1, which drops tied top earners
- ✗Confusing above-average with the maximum
Follow-up questions
- →How would you also return only those hired this year?
- →Why can LIMIT 1 give a wrong answer here?
JuniorCodeCommonTop-5 products by total revenue, with quantity sold and a deterministic tie-break
Top-5 products by total revenue, with quantity sold and a deterministic tie-break
Aggregate per product, then order and cap: SELECT product_id, SUM(quantity) AS qty, SUM(quantity*unit_price) AS revenue FROM order_items GROUP BY product_id ORDER BY revenue DESC, product_id LIMIT 5. The secondary product_id key makes ties deterministic.
Common mistakes
- ✗Selecting product_id without a GROUP BY on it
- ✗Ranking by quantity instead of revenue
- ✗Using LIMIT with no tie-break, so runs differ
Follow-up questions
- →How would you return ties at rank 5 instead of cutting them?
- →What changes if unit_price can be NULL?
JuniorTheoryCommonUNION vs UNION ALL — what differs, and why is UNION the expensive one?
UNION vs UNION ALL — what differs, and why is UNION the expensive one?
UNION ALL concatenates both inputs and keeps every row. UNION does the same but then removes duplicate rows, which forces a sort or hash over the whole result — that de-duplication is the extra cost. Use UNION ALL when duplicates are acceptable; it is faster.
Common mistakes
- ✗Swapping which form de-duplicates the result
- ✗Thinking UNION ALL costs more than UNION
- ✗Expecting UNION to drop only intra-input duplicates
Follow-up questions
- →How do you spot a UNION that should have been UNION ALL?
- →Do the inputs need matching column types for either form?
MiddleDebuggingCommonRevenue doubled after joining orders to a one-to-many shipments table. Diagnose and fix.
Revenue doubled after joining orders to a one-to-many shipments table. Diagnose and fix.
The one-to-many shipments join repeats each order row once per shipment, so SUM(o.amount) adds each order's amount multiple times — join fan-out. Aggregate shipments to one row per order first (a subquery or CTE), then join, or only SUM a shipment-grain value.
Common mistakes
- ✗Summing a parent column across a one-to-many child join
- ✗Blaming a cross join instead of legitimate fan-out
- ✗Trying to average away the duplicated rows
Follow-up questions
- →How would COUNT(DISTINCT o.order_id) help detect fan-out?
- →When is pre-aggregating shipments better than SUM(DISTINCT)?
MiddleCodeCommonFind users who registered but never purchased — and why NOT IN can return zero rows
Find users who registered but never purchased — and why NOT IN can return zero rows
Use an anti-join: SELECT u.user_id FROM users u LEFT JOIN orders o ON o.user_id = u.user_id WHERE o.user_id IS NULL, or NOT EXISTS. NOT IN (SELECT user_id FROM orders) returns zero rows when the subquery holds any NULL, because x NOT IN (…, NULL) is UNKNOWN.
Common mistakes
- ✗Trusting NOT IN when the subquery may contain NULL
- ✗Using INNER JOIN, which drops the unmatched users entirely
- ✗Filtering o.user_id IS NULL after an inner join
Follow-up questions
- →How does NOT EXISTS avoid the NULL problem?
- →Would filtering NULLs out of the subquery fix NOT IN?
JuniorTheoryOccasionalIn what order do SQL clauses run, and why can WHERE not use a SELECT alias while ORDER BY can?
In what order do SQL clauses run, and why can WHERE not use a SELECT alias while ORDER BY can?
Logical order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE runs before SELECT, so a SELECT alias does not exist yet and cannot be referenced. ORDER BY runs after SELECT, so the alias is defined by then. The same order is why HAVING filters aggregates.
Common mistakes
- ✗Assuming clauses run in written (SELECT-first) order
- ✗Thinking WHERE forbids aliases by syntax, not by timing
- ✗Expecting WHERE to filter aggregates like HAVING
Follow-up questions
- →Where does a window function slot into this order?
- →Why can GROUP BY reference a SELECT alias in some engines?
MiddleCodeOccasionalCompute the median order value in a dialect with no MEDIAN function
Compute the median order value in a dialect with no MEDIAN function
Use the ordered-set aggregate PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount), which interpolates the middle value. Without it, number the rows by amount and average the central position(s). A plain AVG returns the mean, not the median.
Common mistakes
- ✗Returning AVG (the mean) instead of the median
- ✗Taking one central row and ignoring the even-count case
- ✗Assuming mean equals median once NULLs are removed
Follow-up questions
- →How does PERCENTILE_DISC differ from PERCENTILE_CONT here?
- →How would you compute a median per group?
MiddleDebuggingRareFixing 'column must appear in GROUP BY' by adding every column explodes the row count. Why?
Fixing 'column must appear in GROUP BY' by adding every column explodes the row count. Why?
The error means a selected column (order_date) is neither grouped nor aggregated. Adding every column to GROUP BY changes the grain to one group per distinct row, so nothing collapses. Fix by grouping on the real key (customer_id) alone, wrapping extras in MAX().
Common mistakes
- ✗Adding every column to GROUP BY to silence the error
- ✗Thinking more GROUP BY columns shrink the result
- ✗Reaching for DISTINCT instead of fixing the grain
Follow-up questions
- →When is grouping by two columns actually what you want?
- →Why does SELECT * with GROUP BY rarely make sense?
MiddleTheoryRarePer-city, per-country and grand-total subtotals in one pass — GROUPING SETS, ROLLUP or CUBE?
Per-city, per-country and grand-total subtotals in one pass — GROUPING SETS, ROLLUP or CUBE?
GROUPING SETS lists the exact grouping combinations to compute in one scan. ROLLUP(country, city) is shorthand for the hierarchical sets {(country,city),(country),()}. CUBE(country, city) produces every subset. GROUPING() flags which columns are rolled up in each row.
Common mistakes
- ✗Thinking ROLLUP and CUBE are aliases of plain GROUP BY
- ✗Believing GROUPING SETS cannot express a grand total
- ✗Treating ROLLUP as symmetric like CUBE
Follow-up questions
- →How do you tell a subtotal row from a NULL data value?
- →When is CUBE the right choice over ROLLUP?
