SQL for Analysts
Query authoring an analyst is grilled on live — JOIN types, GROUP BY/HAVING, set operators, anti-joins and subqueries, views, window functions, and reading a broken query.
14 questions
JuniorCodeVery commonEvery customer with their total order amount, including those who ordered nothing
Every customer with their total order amount, including those who ordered nothing
LEFT JOIN from customers to orders keeps customers with no order, then GROUP BY the customer and use COALESCE(SUM(o.amount), 0) so the NULL from unmatched rows becomes 0. An INNER JOIN would silently drop everyone who never ordered.
Common mistakes
- ✗Using INNER JOIN and losing every customer who never ordered
- ✗Assuming SUM over the unmatched side already returns 0 without COALESCE
- ✗Adding a WHERE on the joined column that re-drops the zero-order customers
Follow-up questions
- →Why does SUM over the unmatched side return NULL rather than 0?
- →How would you also show the count of orders per customer in the same query?
JuniorTheoryVery commonWhich JOIN types exist, and what does a LEFT JOIN return that an INNER JOIN does not?
Which JOIN types exist, and what does a LEFT JOIN return that an INNER JOIN does not?
Main types — INNER, LEFT, RIGHT, FULL, CROSS. INNER keeps only rows that match in both tables. LEFT returns every left row, filling the right columns with NULL where no match exists — so it preserves the unmatched left rows that INNER silently drops.
Common mistakes
- ✗Believing LEFT and INNER differ only in speed, not in which rows survive
- ✗Thinking a LEFT JOIN keeps unmatched rows from both sides like a FULL JOIN
- ✗Forgetting that unmatched right-side columns come back as NULL, not blank
Follow-up questions
- →When would a RIGHT JOIN be preferred over rewriting it as a LEFT JOIN?
- →How does a FULL OUTER JOIN differ from a LEFT JOIN here?
JuniorTheoryVery commonWhat is the difference between WHERE and HAVING, and why can an aggregate not be filtered in WHERE?
What is the difference between WHERE and HAVING, and why can an aggregate not be filtered in WHERE?
WHERE filters individual rows before grouping; HAVING filters whole groups after aggregation. An aggregate like SUM(x) cannot appear in WHERE, which runs before grouping, so no aggregate value exists yet. Aggregate conditions belong in HAVING.
Common mistakes
- ✗Putting an aggregate condition in WHERE and getting a syntax error
- ✗Believing WHERE and HAVING are interchangeable aliases
- ✗Thinking HAVING filters rows rather than already-formed groups
Follow-up questions
- →Can a query use both WHERE and HAVING, and in what order do they apply?
- →Where does an alias defined in the SELECT list become usable — WHERE or HAVING?
JuniorTheoryCommonHow do TRUNCATE, DELETE and DROP differ, and which of them can be rolled back?
How do TRUNCATE, DELETE and DROP differ, and which of them can be rolled back?
DELETE removes chosen rows transactionally, so ROLLBACK inside a transaction undoes it. TRUNCATE wipes the whole table fast; DROP removes the table itself. On most engines TRUNCATE and DROP are auto-committing DDL and cannot be rolled back.
Common mistakes
- ✗Believing TRUNCATE is transactional and can be undone like DELETE
- ✗Thinking DROP only removes rows and leaves the empty table behind
- ✗Assuming all three are equivalent apart from execution speed
Follow-up questions
- →Why does DELETE reset less state than TRUNCATE, e.g. auto-increment counters?
- →On what engines can TRUNCATE actually be transactional?
JuniorTheoryCommonWhat is the difference between UNION and UNION ALL, and what must the two SELECTs share?
What is the difference between UNION and UNION ALL, and what must the two SELECTs share?
Both stack two SELECT results vertically. UNION removes duplicate rows, costing an extra pass; UNION ALL keeps every row, duplicates included, and is faster. Both need the two SELECTs to share column count and compatible, position-matched types.
Common mistakes
- ✗Assuming UNION ALL deduplicates and UNION keeps everything
- ✗Believing the two SELECTs may return different column counts
- ✗Reaching for UNION when duplicates are impossible and paying for the sort
Follow-up questions
- →When is UNION ALL the correct choice even though duplicates could appear?
- →How are column names of the combined result decided — from which SELECT?
MiddleCodeCommonRows of table A with no match in table B — give two different ways
Rows of table A with no match in table B — give two different ways
Way one — LEFT JOIN b ON b.a_id = a.id WHERE b.a_id IS NULL, keeping left rows whose right side stayed unmatched. Way two — WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id). Prefer NOT EXISTS to NOT IN, which breaks if the subquery returns a NULL.
Common mistakes
- ✗Reaching for INNER JOIN, which returns the matches instead of the misses
- ✗Assuming NOT IN and NOT EXISTS behave identically when a NULL is present
- ✗Filtering on IS NOT NULL of the wrong side and inverting the result
Follow-up questions
- →Why does a NULL in the subquery break NOT IN but not NOT EXISTS?
- →How does the planner typically execute the LEFT JOIN / IS NULL form?
MiddleCodeCommonCount orders per status, showing 0 for statuses that have no orders
Count orders per status, showing 0 for statuses that have no orders
Drive from statuses with a LEFT JOIN to orders, so an order-less status still yields a row. Use COUNT(o.id) — the joined column, not COUNT(*) — because COUNT over the unmatched side's NULL gives 0, while COUNT(*) counts the padding row as 1.
Common mistakes
- ✗Using INNER JOIN, which drops the statuses that have no orders
- ✗Using COUNT(*) on the outer join and counting the padding row as 1
- ✗Assuming an empty scalar subquery returns 0 rather than NULL
Follow-up questions
- →Why does COUNT(o.id) give 0 while COUNT(*) gives 1 for an empty status?
- →How would you also show total order amount per status in the same query?
MiddleCodeCommonFind rows duplicated by (email, phone) and keep only the earliest one
Find rows duplicated by (email, phone) and keep only the earliest one
Number the rows within each (email, phone) pair by age and keep the first — ROW_NUMBER() OVER (PARTITION BY email, phone ORDER BY created_at) AS rn in a subquery, then filter rn = 1. A plain GROUP BY would collapse the group but could not return the whole earliest row.
Common mistakes
- ✗Expecting DISTINCT to pick the earliest row rather than exact duplicates
- ✗Assuming MIN(created_at) drags the other columns from the same row
- ✗Relying on physical row order instead of an explicit ORDER BY
Follow-up questions
- →How would you break ties when two rows share the same created_at?
- →How does this query change if you must delete the duplicates in place?
MiddleTheoryCommonWhat does NULL do to an equality test, to COUNT(), and to NOT IN (subquery)?
What does NULL do to an equality test, to COUNT(), and to NOT IN (subquery)?
NULL means unknown, so x = NULL yields UNKNOWN, never true — test with IS NULL. COUNT(*) counts every row, but COUNT(col) skips rows where col is NULL. And NOT IN (subquery) returns nothing once the subquery holds any NULL.
Common mistakes
- ✗Filtering nulls with
= NULLinstead ofIS NULL - ✗Assuming COUNT(col) counts rows where the column is NULL
- ✗Using NOT IN against a subquery that can yield NULL and losing all rows
Follow-up questions
- →How does three-valued logic change what a WHERE clause returns?
- →How would you rewrite a NOT IN that risks a NULL to be safe?
MiddleCodeCommonThe three highest-paid employees in each department
The three highest-paid employees in each department
Rank inside each department with a window function, then filter. Put ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) in a subquery and keep rows where the number is <= 3. Use RANK() to keep employees tied at the third salary.
Common mistakes
- ✗Using a global LIMIT 3 that returns three rows overall, not per department
- ✗Trying to pull ranks 1-3 with repeated MAX aggregates
- ✗Confusing RANK and ROW_NUMBER when the third salary is tied
Follow-up questions
- →How do RANK, DENSE_RANK and ROW_NUMBER differ on tied salaries?
- →How would you write this without window functions, using a correlated subquery?
JuniorTheoryOccasionalWhat is a view, what is a materialized view, and when do you use each?
What is a view, what is a materialized view, and when do you use each?
A view is a stored query — a virtual table re-run on every read, always current and storage-free. A materialized view stores results physically — fast reads, but stale until refreshed. Use views for live data, materialized ones for heavy, frequent queries.
Common mistakes
- ✗Believing a plain view stores its rows physically like a table
- ✗Thinking a materialized view stays automatically current without a refresh
- ✗Confusing a materialized view with a session-scoped temporary table
Follow-up questions
- →What triggers a materialized view refresh, and can it be incremental?
- →Can you write through a view to update the underlying table?
SeniorDebuggingOccasionalA non-aggregated column sits beside COUNT(*) with a partial GROUP BY and returns nonsense — fix it
A non-aggregated column sits beside COUNT(*) with a partial GROUP BY and returns nonsense — fix it
department_name is neither grouped nor aggregated, which is invalid SQL. Strict engines reject it; MySQL in a loose mode silently returns an arbitrary row's value — the nonsense seen. Fix it by adding department_name to GROUP BY or wrapping it in MIN/MAX.
Common mistakes
- ✗Assuming a functionally-dependent column is carried automatically
- ✗Blaming display or collation instead of the ungrouped-column rule
- ✗Thinking COUNT(*) itself is what makes the query invalid
Follow-up questions
- →Why do strict and loose SQL modes disagree on whether this even runs?
- →When is a functional-dependency exception to GROUP BY actually allowed?
SeniorDebuggingOccasionalTRUNCATE then ROLLBACK, but the rows are still gone — explain why
TRUNCATE then ROLLBACK, but the rows are still gone — explain why
On MySQL and many engines TRUNCATE is DDL, and DDL triggers an implicit COMMIT of the open transaction before it runs. So TRUNCATE committed the instant it ran, and the later ROLLBACK had nothing to undo. A DELETE is DML and would have rolled back.
Common mistakes
- ✗Believing TRUNCATE participates in the surrounding transaction like DELETE
- ✗Blaming timing or disk flushing rather than the implicit commit
- ✗Thinking ROLLBACK cannot undo any row removal at all, even DELETE
Follow-up questions
- →Which other statements cause the same implicit commit before they run?
- →How would you rewrite the operation so it is genuinely rollback-safe?
SeniorCodeRareCustomers who have ordered every product in a given category
Customers who have ordered every product in a given category
Group each customer's category orders and keep those whose distinct product count equals the category's total — HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM products WHERE category_id = :cat). DISTINCT stops repeat orders inflating the count.
Common mistakes
- ✗Using IN, which returns customers who ordered any product, not all
- ✗Counting without DISTINCT so repeat orders inflate the match
- ✗Comparing with >= instead of = and letting extra products through
Follow-up questions
- →How would a double-NOT-EXISTS formulation express the same division?
- →Why is DISTINCT essential when a customer can reorder the same product?
