Recognize a problem, then compose a solution
Interview questions often combine a few operations across different grains. The wording map suggests a starting point; the worked lessons show why the stages are needed and where a plausible shortcut breaks.
Suggested route: Try a pattern with a tiny fixture, then change the population or tie rule. Use mixed practice when you can identify a technique without its label.
By the end: Describe the sequence of transformations and reject an alternative with a concrete counterexample.
Wording → first technique
| Interview wording | First technique to consider |
|---|---|
| latest / earliest row | ROW_NUMBER or PostgreSQL DISTINCT ON |
| exactly top N rows per group | ROW_NUMBER |
| top N distinct values / levels | DENSE_RANK |
| previous / next observed value | LAG / LEAD |
| month-over-month / period change | aggregate → LAG |
| percentage of total | aggregate → SUM(...) OVER () |
| at least one related row | EXISTS |
| never / no matching row | NOT EXISTS |
| every required item | double NOT EXISTS or HAVING COUNT(DISTINCT ...) |
| both/all requested categories | conditional aggregation / HAVING |
| consecutive dates | deduplicate → row-number island key |
| new session after gap | LAG → boundary flag → cumulative SUM |
| state/run changes | LAG → changed flag → cumulative SUM |
| missing dates / zero-activity periods | calendar spine → LEFT JOIN |
| first event then later event | first-event CTE → join/existence after timestamp |
| ordered funnel | stage timestamps / ordered existence checks |
| median / p95 / p99 | PERCENTILE_CONT ... WITHIN GROUP |
| actual observed percentile value | PERCENTILE_DISC ... WITHIN GROUP |
| bucket users into quartiles/deciles | NTILE |
| relative rank of each row | PERCENT_RANK / CUME_DIST |
| pivot categories to columns | conditional aggregation |
| overlapping intervals | self join + overlap condition |
| all entity × period combinations | CROSS JOIN + LEFT JOIN |
High-value composition patterns
Aggregate → window
Use whenever a comparison should happen after reducing data to the reporting grain.
Examples: MoM revenue, rank stores by total visits, percentage of total, cumulative monthly sales.
LAG → arithmetic
current metric
- previous metric
= absolute change
(current - previous) / previous
= percentage change
Protect a zero previous value with NULLIF.
LAG → flag → cumulative SUM
- Ordered events
- LAG: previous event or state
- CASE: mark each boundary
- Cumulative SUM of boundaries
- Session, island, or run ID
Use for sessions, state changes, event runs, and many gaps-and-islands problems.
ROW_NUMBER → filter
- Partition by business key
- Order by preferred survivor
- Assign ROW_NUMBER
- Keep rn = 1
Use for latest records and deterministic deduplication.
Calendar → LEFT JOIN
- Generate expected dates
- Aggregate observed data
- LEFT JOIN on the date
- Fill zero only when appropriate
Needed when “no row” must appear explicitly as a date with zero activity.
CROSS JOIN → LEFT JOIN
- Expected entities
- CROSS JOIN expected categories or dates
- LEFT JOIN observations
Use for students × subjects, stores × days, customers × months.
First event → later event
- MIN(event_time) per entity
- Establish first-event attributes
- Find events after first_time
Useful for first purchase, repeat purchase, activation, campaign follow-up, and retention questions.
Percentile decision map
| Question | Use |
|---|---|
| “What is the p95 threshold?” | PERCENTILE_CONT(0.95) WITHIN GROUP |
| “Give an actual observed value at the percentile” | PERCENTILE_DISC |
| “Where does each row rank from 0–1?” | PERCENT_RANK() |
| “What fraction is at or below this row?” | CUME_DIST() |
| “Split rows into 10 groups” | NTILE(10) |
Output-grain checks
Before writing the query, state:
- What does one input row represent?
- What should one output row represent?
- Are duplicates meaningful?
- Can dates/periods be absent?
- How should ties be handled?
- Does “previous” mean previous row or previous calendar period?
- What population belongs in the denominator?
These decisions usually determine the correct SQL pattern before syntax does.
In this chapter
- Duplicates & latest recordsFind repeated business keys and choose one version deterministically.
- Consecutive days & streaksGroup runs using deduplicated dates and row numbers.
- Sessions & changes in stateTurn boundary flags into groups with a cumulative sum.
- Retention & missing datesDefine observation windows and construct complete calendars.
- Pivots, medians & interval overlapsRecognize several useful extensions of the core patterns.
- Ordered funnelsRequire the right sequence of events instead of merely counting users with each event.
- Period changes and missing monthsAggregate to calendar grain before LAG, and make missing periods explicit.
- Rates, populations, and weighted averagesDefine numerator and denominator at the same grain before dividing.
How to study
Read the wording column and try to name the technique before looking right. In an interview, write the output grain and edge case before writing syntax.