The SQL patterns that show up in almost every data interview
Eight query shapes cover the large majority of SQL rounds. Drill these until they are boring and the round stops being a filter.
SQL rounds look varied and are not. Once you have sat through enough of them, the same eight shapes keep reappearing with different table names.
1. Aggregate with a filter on the aggregate
Counting per group, then keeping only groups that pass a condition. The trap is trying to filter in WHERE instead of HAVING, or filtering the rows before grouping when the question wanted the opposite.
Ask yourself: does the condition apply to a row, or to a group? That single question resolves most of these.
2. Ranking inside a group
Top three products per category, most recent order per customer, second-highest salary per department. Window functions own this space.
ROW_NUMBER() when ties must be broken arbitrarily, RANK() when ties should share a position and skip the next, DENSE_RANK() when they should not skip. Interviewers ask which you picked and why.
3. Running totals and moving averages
Cumulative revenue by day, seven-day rolling average. Same window syntax with a frame clause. Know the difference between ROWS BETWEEN and RANGE BETWEEN when your dates have gaps — this is a favourite follow-up.
4. Period-over-period comparison
This month against last month, this week against the same week last year. LAG() and LEAD() do it in one pass. The self-join version works too, but it is slower to write and easier to get wrong under time pressure.
5. Gaps and islands
Consecutive login streaks, uninterrupted subscription periods, sessions built from timestamped events. The standard trick is to subtract a row number from the date so that consecutive runs share a constant, then group by that constant.
It looks like a puzzle the first time and becomes routine the third time. Practise it specifically.
6. Funnels
Users who viewed, then added to cart, then bought. Usually conditional aggregation over an events table rather than a chain of joins, because joins multiply rows and quietly inflate your counts.
7. Anti-joins
Customers with no orders, products never reviewed. LEFT JOIN ... WHERE right.id IS NULL or NOT EXISTS. Be ready to explain why NOT IN is dangerous when the subquery can return a null — that is the whole point of the question.
8. Deduplication
Keeping one row per entity, usually the latest. ROW_NUMBER() partitioned by the entity and ordered by the timestamp, then filter to the first row. Then the follow-up arrives: what if two rows share the same timestamp? Have an answer.
How to practise
Do not do a hundred easy problems. Take one realistic schema — users, events, orders, products — and write all eight shapes against it. Then write them again a week later without looking.
The signal an interviewer is reading is not whether you know window functions. It is whether you paused to ask about duplicates, nulls and time zones before you started typing.