The SQL Interview: Query Patterns

"In theory, theory and practice are the same. In practice, they are not." — attributed to Yogi Berra (and every engineer who has bombed a live SQL screen)

You know SQL. You have written SELECT, joined tables, grouped and aggregated. And yet the live SQL screen — a shared editor, a ticking clock, an interviewer watching your cursor — has a way of erasing all of that. The reason is rarely knowledge. It is recognition. Interview SQL is not a fresh puzzle each time; it is a small deck of recurring shapes, dealt over and over with the table names swapped. Learn the shapes and the panic evaporates, because you stop inventing a query and start retrieving one.

This chapter is that deck. Eight patterns cover the overwhelming majority of what you will be asked in a screen: the Nth-highest value, top-N per group, finding duplicates, rows with no match, running totals, pivots, comparing a row to its neighbour, and grouped aggregates with a filter. For each you get the problem in plain words, the approach, and a runnable template you can lift and re-point at whatever tables the interviewer gives you.

Treat every block below as a keyboard, not a page. Run it, then break it — change the column, flip the sort, raise the threshold — until the shape is in your fingers, not just your notes. That muscle memory is exactly what the screen is testing.

Pattern 1 — The Nth-highest value

Problem. "Find the second-highest order amount." This is the single most common opener in the deck, usually phrased as second or third highest salary, price, or score.

Approach. Two idioms, and you should know both because interviewers often ask for the other one. The first is ORDER BY ... DESC with LIMIT 1 OFFSET (N-1) — skip the top N−1 rows, take the next. Use DISTINCT so that ties don't burn a slot.

That returns 45000 — the amounts descend 250000, 45000, 3000, 3000, 500, 500, and offsetting past the distinct maximum lands on the runner-up. Change OFFSET 1 to OFFSET 2 and you get the third-highest.

The second idiom uses a window function, and it is the one to reach for when the interviewer adds "…and do it without LIMIT" or "…now generalize to the Nth." DENSE_RANK() numbers rows by value, giving tied values the same rank and not leaving gaps — exactly what "Nth distinct highest" means.

Pattern 2 — Top-N per group

Problem. "For each customer, return their single largest order." The tell is the phrase per (per customer, per department, per category) combined with top / most recent / best. Plain GROUP BY collapses each group to one aggregate row and loses the other columns — you cannot ask "which order?" that way. You need to rank within each group and keep the winners.

Approach. ROW_NUMBER() OVER (PARTITION BY <group> ORDER BY <metric> DESC). PARTITION BY restarts the numbering for every group; filter to = 1 for the top one, <= 3 for the top three.

Each customer appears once, showing their biggest order — Ada's Book (3000) beats her Pen (500), while customers with a single order simply return it. Swap ROW_NUMBER for RANK if the interviewer wants all orders tied for the top rather than an arbitrary one.

Pattern 3 — Find duplicates

Problem. "Which products were ordered more than once?" or, in the classic form, "find duplicate emails in the users table." A data-quality staple, and the gateway to a follow-up about deleting the duplicates.

Approach. GROUP BY the column(s) that define a duplicate, then HAVING COUNT(*) > 1. WHERE filters rows before grouping; HAVING filters groups after — you need HAVING because the count only exists once the group is formed.

Book and Pen each appear twice; Laptop and Chair are filtered out. To dedupe on a combination — say a customer ordering the same product twice — just group by both columns: GROUP BY customer_id, product HAVING COUNT(*) > 1.

Pattern 4 — Rows with no match (anti-join)

Problem. "List customers who have never placed an order." The universal shape is "X with no matching Y" — products never sold, users with no logins, employees with no manager. It is an anti-join: you want the rows in one table that have no partner in another.

Approach. Two idioms again. The first is LEFT JOIN the other table, then keep only the rows where the join failed — WHERE <right>.something IS NULL. Our seed happens to give every customer an order, so the block first adds one order-less customer, then finds them. (The database resets on every run, so this is repeatable.)

Ivy is the only customer with no matching order, so the failed join leaves her orders columns NULL, and the WHERE isolates her. The second idiom reads more like the English sentence — NOT EXISTS with a correlated subquery — and is often the clearer choice:

Pattern 5 — Running total

Problem. "Show each order with a cumulative sum of amounts over time." Running totals, running counts, and cumulative averages all share this shape — a total that grows as you walk down the rows.

Approach. An aggregate used as a window: SUM(amount) OVER (ORDER BY id). Adding ORDER BY to a window turns the plain aggregate into a cumulative one — each row sees itself plus everything ordered before it. Without ORDER BY, SUM(...) OVER () gives the grand total on every row instead (itself a handy trick for "% of total").

The running_total column climbs 3000, 3500, 253500, … as each order is added in. Partition it — SUM(amount) OVER (PARTITION BY customer_id ORDER BY id) — and the total resets per customer, the pattern behind "cumulative spend per user."

Pattern 6 — Pivot / conditional aggregation

Problem. "For each customer, show total spend on Books in one column and total spend on Pens in another." Turning rows into columns — a pivot — is how you build report-shaped output: months across the top, categories as columns, one row per entity.

Approach. SUM(CASE WHEN <condition> THEN <value> ELSE 0 END), one such expression per output column, wrapped in a GROUP BY. The CASE picks which rows contribute to which column; the SUM collapses them.

Each customer becomes one row with a column per product family. The same trick counts instead of sums — COUNT(CASE WHEN status='paid' THEN 1 END) — which is how you produce "paid vs unpaid orders side by side."

Pattern 7 — Compare a row to its neighbour

Problem. "For each order, show the previous order's amount and the change from it." Month-over-month growth, day-over-day deltas, "time since last event" — all need a row to see its neighbour.

Approach. LAG(col) OVER (ORDER BY ...) fetches the value from the previous row; LEAD fetches the next. Give LAG a default (LAG(amount, 1, 0)) so the first row — which has no predecessor — shows 0 instead of NULL.

Each row now carries the previous order's amount and the delta — the exact engine behind "growth vs last period." Partition by a group (PARTITION BY customer_id ORDER BY id) and each customer's comparison stays inside their own history rather than bleeding across customers.

Pattern 8 — Grouped aggregates with a filter

Problem. "List customers whose total spend exceeds 3,000." The bread-and-butter shape: aggregate per group, then keep only the groups that clear a bar. It usually rides on top of a join, since the thing you group by and the thing you sum often live in different tables.

Approach. JOIN, then GROUP BY the entity, SUM/COUNT/AVG the metric, and HAVING to filter on that aggregate. Remember the logical order: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE filters rows before grouping; HAVING filters groups after.

Lin (250000), Zoe (45000), and Ada (3500) clear the bar; Sam (3000) and Tom (500) fall below it and drop out. Note that we GROUP BY c.id (the key) as well as the name — grouping by a name alone risks merging two people who share it.

Common Mistakes

• Using WHERE where you need HAVING (or vice-versa). You cannot filter on COUNT(*) in WHERE — the aggregate doesn't exist yet. Filter individual rows in WHERE, filter aggregated groups in HAVING.
• GROUP BY and selecting a non-aggregated column. Every column in the SELECT must be either grouped-by or wrapped in an aggregate. SQLite will let you select a stray column and return an arbitrary value — a portability footgun that errors in Postgres and strict MySQL. Don't rely on it.
• NOT IN with a NULL-able subquery. As in Pattern 4 — one NULL and NOT IN returns nothing. Reach for NOT EXISTS.
• ROW_NUMBER when you meant DENSE_RANK (or the reverse). "Top 3 distinct salaries" and "any 3 top-paid employees" are different questions; the wrong function quietly gives the wrong answer.
• Forgetting ORDER BY inside a window. SUM(x) OVER () is a grand total; SUM(x) OVER (ORDER BY d) is a running total. The clause changes the meaning entirely.
• Ranking without PARTITION BY when the question said "per". You get one global ranking instead of one per group — a top-1-overall answer to a top-1-per-customer question.

Summary

• Interview SQL is a small deck of recurring shapes; recognition beats invention. Learn the eight and most screens become retrieval, not puzzle-solving.
• Nth-highest: LIMIT/OFFSET for quick, DENSE_RANK for portable and generalizable. Top-N per group: ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) then filter the rank.
• Duplicates: GROUP BY … HAVING COUNT(*) > 1. No match / anti-join: LEFT JOIN … IS NULL or NOT EXISTS — never NOT IN against NULL-able columns.
• Running total: aggregate as a window with ORDER BY. Pivot: SUM(CASE WHEN … ). Neighbour comparison: LAG/LEAD. Grouped filter: GROUP BY … HAVING on the aggregate.
• Know why each choice matters — RANK vs DENSE_RANK vs ROW_NUMBER, WHERE vs HAVING, the NOT IN NULL trap — because the follow-up question always probes the why.
• Practice by predicting output before running; that is the exact habit the live screen rewards.

Next, we widen the lens from writing the query to reasoning about how the engine runs it — reading a query plan, spotting the full-table scan, and answering the "now make it fast" follow-up that turns a good SQL answer into a great one.