AdvancedSQL · 25 min
Top N Per Group: Advanced
At the staff and principal level, the top-N-per-group question is not a SQL test. It is a stand-in for a larger system design conversation. The interviewer will ask the question, listen to your query, and then escalate: how does this scale, what materialization strategy do you use, how do you bound the freshness against the cost, what happens when the underlying table partitions change. The query is twelve lines. The conversation around it is the rest of the hour. This lesson is about what to say when the conversation moves past the SQL and into the architecture.
Treat the top-N query as the surface of a designed report, with its own contract and refresh cadence
Reason about the query plan across single-node, distributed, and incremental execution models
Defend the materialization decision against alternatives the interviewer will propose
Articulate the production failure modes (data skew, partition drift, late-arriving updates) and the mitigations
"Now Do It Without Window Functions"
Identify when a question requires per-group ranking instead of a global ORDER BY LIMIT.
The question opens with 'top 3 highest-paid employees per department.' By the third follow-up, the conversation is about how to maintain that view continuously as the employees table changes, what happens when a region's headcount triples, and whether the right answer is a materialized view, a precomputed table, or an on-demand query. The SQL itself is settled in the first two minutes. The remaining time is about whether you can design the system that owns the SQL.
You are being tested on top N per group when you hear:
- ▸"top N highest/lowest X per group"
- ▸"the most-recent N records for each customer"
- ▸"for every department, return the top performer"
- ▸"top N including ties" / "top N salary levels" (tells: RANK vs DENSE_RANK)
- ▸"how would you keep this updated as the data changes?" (the materialization escalation)
What the interviewer is actually testing at this level
The question has three layers and the interviewer scores each one independently. Layer one: do you produce the right query with the right tie-handling function and a deterministic tiebreaker? Most candidates clear this. Layer two: can you reason about the supporting infrastructure (index, cluster key, partition layout) that makes the query viable at billion-row scale? Some candidates clear this. Layer three: can you design the materialization and refresh strategy, defend it against alternatives, and articulate the failure modes you have seen in production? Most of the conversation lives here.
How the loop scores this question silently:
- ▸Layer 1: correct query with deterministic tiebreaker (most candidates clear)
- ▸Layer 2: supporting infrastructure (index, cluster key, partition layout)
- ▸Layer 3: materialization and refresh strategy with production failure modes
- ▸All three layers must surface for a strong-hire verdict
•Answer that stops at the query
- Writes ROW_NUMBER with a tiebreaker, defends the function choice cleanly
- Discusses indexes when prompted
- Goes thin when the interviewer asks 'how do you keep this updated'
- Treats the question as primarily a SQL question
✓Answer that owns the system
- Writes the query in two minutes and then narrates the architecture
- Names the materialization strategy unprompted (table, view, or on-demand)
- Defends the strategy against the refresh-vs-cost tradeoff
- Anticipates the production failure modes: skew, drift, late updates
Why this question stays in staff loops
Top N per group is a microcosm of every reporting design question a staff engineer faces. The query has business semantics (tie handling), physical considerations (indexes and partitions), and operational concerns (refresh cadence, downstream consumers, failure modes). A loop that asks this question is not testing SQL. It is testing whether you reach for the right level of the stack when the conversation moves. The interviewer is watching whether you pivot smoothly from query to schema to materialization to operations, or whether you stay at one level and let the interviewer drag you up.
Correlated-Subquery Top-N Fallback
Write a correct top-N-per-group query as a correlated subquery (no window functions), with a tie-breaking condition that encodes the ORDER BY contract and matches ROW_NUMBER semantics.
At this level the interviewer often removes window functions from the table: 'now do it without ROW_NUMBER.' The move that earns the credit is not just producing a working correlated subquery; it is producing one whose tie-breaking condition is a deliberate contract, then pivoting the conversation to where the query actually lives in production: as a materialized table, refreshed on a cadence, fed by a partitioned source, consumed by a dashboard with a documented freshness SLA.
The correlated-subquery form, with the tiebreaker contract
Narrating the subquery to the interviewer
Read the correlated subquery aloud as a contract. 'For each row e1, I count how many employees in the same department strictly outrank it. If fewer than three beat it, it is in the top three. The correlation on department_id makes each department's ranking independent, the same job PARTITION BY did. The strict-outranks condition is my ORDER BY, written as a predicate: higher salary wins, ties fall to earlier hire_date to favor tenure, and the final tie on employee_id guarantees determinism.' That narration is the move. It tells the interviewer that the cascade of OR clauses is not clutter; it is exactly the ORDER BY salary DESC, hire_date ASC, employee_id ASC contract, expressed as a comparison.
The choice the interviewer is silently scoring
The order of the OR clauses encodes business policy, exactly as the ORDER BY column order did in the window-function form. Salary first means 'compensation determines the top.' Hire_date second means 'tenure breaks ties.' A performance_rating clause there instead would mean 'recent performance breaks ties.' Each choice is a different report. There is also a correctness fork the interviewer is watching for: drop the tie-breaking OR clauses entirely and the query counts only strictly-greater salaries, which is RANK <= 3, not ROW_NUMBER <= 3. Under RANK, two employees tied for second both pass as rank 2 and you can return more than three rows per department. The unique-column tiebreaker is what converts RANK semantics into exactly-N. Naming that fork unprompted is the signal that earns the credit: the query is not separate from the business rule; it is the business rule, written in SQL.
The OR cascade in the subquery is the ORDER BY, rewritten as a predicate. Read them side by side: ORDER BY salary DESC, hire_date ASC, employee_id ASC becomes 'e2 outranks e1 if higher salary, or equal salary and earlier hire_date, or equal on both and lower employee_id.' The last clause must key on a unique column, or ties never fully break and you get RANK, not exactly-N.
Where this query actually lives
In a real production system, this query is not run on demand. It feeds a materialized table that is refreshed nightly (or hourly, if the source data changes that often), and the dashboard reads from the materialized table. The query above is the body of the refresh job, not the body of the user request. This matters more for the correlated form than the window form: the subquery re-scans the department for every row, so its cost is quadratic in department size and you would never put it on a serving path. State this orientation unprompted: 'I would treat this as the materialization query, not the serving query. The dashboard queries a top_employees_per_dept table that this query maintains, and I would reach for the window-function form as the refresh body once ROW_NUMBER is back on the table.' That single sentence reframes the entire conversation from SQL to system design.
Top-N Performance and Index Strategy
Choose between ROW_NUMBER, RANK, and DENSE_RANK based on whether ties should inflate, collapse, or be broken arbitrarily.
By this layer, the choice between ROW_NUMBER, RANK, and DENSE_RANK is not a syntax memory test. The interviewer is checking whether you can map the business question to the right function and articulate the failure modes of picking wrong. The function choice is a contract; the contract has consequences when ties exist in real data.
The function-choice decision as a contract
Each function answers a different question. ROW_NUMBER answers 'who are the N people we want to act on, exactly N, ties broken by policy.' RANK answers 'what are the top N positions, with ties sharing positions and consuming additional rows.' DENSE_RANK answers 'what are the top N distinct levels, with everyone at those levels included.' Naming the consumer's question is what determines the right function. The interviewer is watching whether you connect those dots without being told.
Concrete failure mode for each wrong choice
Wrong function choices cause specific, recognizable production bugs. Picking ROW_NUMBER when the consumer wanted RANK: the bonus pool excludes employees who should have been included because they tied for third. Picking RANK when the consumer wanted ROW_NUMBER: the bonus pool overflows because too many employees tied for second. Picking DENSE_RANK without realizing it: the count balloons unpredictably when the source data has high-cardinality ties. Each wrong choice produces a quiet bug that surfaces only when finance or HR audits the bonus pool. Mention these failure modes when discussing the function choice; the specificity is what proves you have lived through one.
| Function | Tie behavior | Total rows per group | Bug surface |
|---|---|---|---|
| ROW_NUMBER | Tiebreaker breaks tie | Exactly N | Wrong tiebreaker → non-determinism, wrong winners |
| RANK | Ties share rank, gap follows | ≥ N | Tied positions inflate the result silently |
| DENSE_RANK | Ties share rank, no gap | ≥ N | Many employees at top distinct salaries explodes the count |
The NULL question at scale
On a 1000-employee dataset, a few NULL salaries are noticeable. On a 10-million-employee dataset across hundreds of source systems, NULLs become a steady-state condition. The data has NULLs in salary for interns, leave-of-absence employees, contract workers, employees mid-promotion, and rows that failed an upstream join. The query must handle them deterministically. NULLS LAST is the floor. Above that, the right move is to decide whether NULL-salary rows should appear in the ranking at all, and if not, to filter them out in the source CTE. Stating 'I would filter NULL salary rows in the source before ranking, unless the consumer wants them ranked last' is what proves you have built reporting against real HR data.
The dialect quirk that matters at scale
Distributed query engines (BigQuery, Snowflake, Presto) can produce non-deterministic results when ROW_NUMBER's ORDER BY does not break all ties, because parallel workers may see rows in different orders. On a single-node engine, the same query against the same data tends to produce the same result for the wrong reason: the engine's row scan happens to be stable. Code that works in development against Postgres may produce non-deterministic results in production against Snowflake. The fix is the same as always: include a unique column in the tiebreaker. The lesson is that 'works in dev' is not 'works in prod' on distributed engines. Articulating this distinction is what tells the interviewer you have shipped against more than one engine.
Distributed engines reward queries that are explicit about determinism. 'The tiebreaker column guarantees the same result on parallel workers; without it, two workers can see the same tied rows in different orders and produce different ROW_NUMBER assignments.' That sentence is what distinguishes a candidate who has tuned for a single engine from a candidate who has shipped against many.
Lateral / CROSS APPLY Top-N Per Row
Solve top-N-per-group using a correlated subquery or self-join when the interviewer bans window functions.
At this level the no-window question becomes a probe for whether you understand the optimizer's behavior across engines. The two fallback forms are familiar: correlated subquery and self-join. The senior conversation is about which one the engine actually plans well and what it costs when the plan goes wrong.
The correlated subquery plan, by engine
On a modern engine, the correlated subquery 'WHERE (SELECT COUNT(*) FROM e2 WHERE ... ) < 3' is rewritten by the optimizer into a hash-based set operation. The engine builds a hash of (department, salary) pairs, then evaluates each outer row against the hash. This plan is O(N) with a constant factor for the hash build. On older engines (Postgres < 12, MySQL < 8, Hive 1.x), the rewrite is not always performed, and the subquery executes literally: once per outer row. For an N-row table with D-row departments, the cost becomes O(N * D), which is catastrophic. Knowing which engines do the rewrite and which do not is the production knowledge the interviewer is checking for.
The self-join fallback
The self-join produces the same RANK semantics as the basic correlated subquery: ties share rank, gaps appear after ties. The plan is more predictable across engines: the optimizer treats it as a standard outer join followed by a GROUP BY, and the cost model is well-understood. On engines where the correlated subquery does not rewrite, the self-join is the form to reach for. State this explicitly: 'on engines where the correlated subquery plans poorly, I prefer the self-join because the plan is more predictable.' That sentence is the move that connects SQL knowledge to engine knowledge.
The semantic conversion that distinguishes the answer
Neither fallback gives you ROW_NUMBER semantics for free. Both produce RANK-style output. To get exactly N rows per group, you have to extend the comparison to include a tiebreaker, which converts the strict-greater-than into a lexicographic comparison.
This is what ROW_NUMBER <= 3 looks like when reconstructed from base SQL. Each ORDER BY column in the window-function version becomes one clause in the tiebreaker chain. Walking through this conversion in the interview shows that you understand the window function as a black box that compiles down to exactly this kind of multi-clause comparison. The depth signal is being able to convert in both directions.
•When the correlated subquery is the right reach
- Engine is modern (Postgres 12+, BigQuery, Snowflake)
- Optimizer rewrites the subquery into a hash semi-join
- Readability matters and the team will revisit the SQL
- RANK semantics are acceptable
✓When the self-join is the right reach
- Engine is older or has weak subquery rewriting
- Plan predictability matters more than syntactic elegance
- You need full control over the join order
- The tiebreaker is multi-column and the subquery becomes unwieldy
Mention specific engine versions in this discussion. 'On Postgres 12 and above, the optimizer rewrites this; on Postgres 11 and below, it does not, and the self-join is required.' Specificity at this layer is the signal. Generic statements about 'older engines' read as bookish; specific version numbers read as someone who has tuned a query on that engine.
Defending the Tie Rule on Performance
Discuss index strategies, partition pruning, and how to handle "top N with ties" at warehouse scale.
The closing layer is the architectural conversation. The interviewer asks how this scales to a billion rows, then how it scales to a billion rows with hourly refresh, then how it scales to a billion rows with hourly refresh on a distributed engine with skewed partition sizes. The answer is layered. Each layer adds a concept; each layer requires you to name a tradeoff. This is the section where the candidate's depth becomes visible.
Layer 1 and 2: physical layout and data skew
On an OLTP database, the supporting index is on (department_id, salary DESC, employee_id). On a warehouse, the equivalent is clustering the table by department_id with a secondary sort on salary. The principle is identical: the engine should be able to read the data in the order the window function needs, without an explicit sort step. Naming this layer-equivalence (index vs cluster) is the move that proves you have tuned both kinds of database. The skew problem sits on top of the physical layout decision. Top N per group is brutally sensitive to skewed partition sizes. If one department has 50 million rows and another has 50, the worker handling the giant department does 1000x the work of every other worker. Window functions cannot parallelize within a partition, only across partitions. The skew turns a parallel query into a sequential one. The mitigation is either to split the giant partition (sometimes feasible if the entity has natural sub-keys), to materialize the top-N for the giant partition separately, or to accept the long-running worker and bound the work elsewhere. State the skew problem unprompted when the interviewer asks about scale: 'if one department dominates, the window function does not parallelize within it, and the query degrades to the cost of that one partition.' That sentence proves you have seen the failure mode.
At Meta in 2021, an ads-quality reporting team had a 'top 50 advertisers per country per day' query that ran cleanly for a year and then started timing out after a single advertiser in the United States bought enough auction volume to make US_advertiser_id contribute ~40% of the daily fact table. The window function partitioned on country; the US partition serialized onto a single worker, and one worker held the entire query hostage. The team's fix had three parts: salt the US partition with a hash of advertiser_id mod 8, materialize the per-country top-50 separately for US versus everyone-else, and add an alert that fires when any single partition exceeds 5% of total rows in a daily fact table. That third item is the one the team still uses in interviews; candidates who name partition-skew alerting unprompted read as someone who has been paged at 3am when the US partition grew too large.
Layer 3: materialization and refresh
On a billion-row source table with hourly refresh, recomputing the top N per group on every refresh is wasteful. The right answer is incremental: maintain a top-N-per-department table, and refresh only the departments whose underlying data changed since the last run. The incremental design is harder to write than the full-refresh design, but it converts an O(N) query into an O(delta) query, which is the difference between a refresh that takes 10 minutes and one that takes 10 seconds. State the incremental approach as the next architectural layer: 'for hourly refresh on a billion-row source, I would maintain a top-N table incrementally, refreshing only the departments with changes since the last run.'
Layer 4: late-arriving updates and contract drift
Reporting tables drift. An employee's salary gets corrected retroactively. A row gets soft-deleted. A department merger changes the partition mapping. The top-N table has to handle these. The interviewer at this level expects you to name the operational concerns even if you do not solve them in the interview. 'For late-arriving salary corrections, I would re-run the affected department's top-N computation. For soft deletes, I would filter them in the materialization query. For partition mergers, I would version the materialized table and pin the dashboard to the current version.' Naming these scenarios shows you have owned a similar table in production.
| Architectural question | Mid-level phrasing | Staff phrasing |
|---|---|---|
| Where does the top-N live? | "In the dashboard query." | "In a top_employees_per_dept table refreshed on the data's cadence; the dashboard does a single point lookup. The window function lives in the refresh job, not the read path." |
| What's the scale bottleneck? | "Sorting each partition." | "The shuffle that co-locates each partition on one worker; once co-located, the sort is cheap. If the source is partitioned by date and queried by department, the shuffle is the dominant cost." |
| What about a single huge partition? | "It runs slower." | "Window functions don't parallelize within a partition. The skew turns a parallel query into a sequential one. Mitigation: salt the giant partition with a sub-key, or materialize it separately, and alert when any partition exceeds 5% of fact-table rows." |
| What about retroactive corrections? | "Re-run the job." | "Versioned materialization keyed on (department_id, refresh_version). Corrections trigger a partial recompute scoped to the affected department; dashboards read the latest version; old versions retained for audit with a TTL cleanup." |
| What if dev uses Postgres and prod uses Snowflake? | "Same SQL, should work." | "Same SQL, different determinism. Postgres tends to be stable for the wrong reason (a stable scan order); Snowflake's parallel passes can produce different ROW_NUMBER assignments on tied rows. The deterministic tiebreaker has to include a unique column to survive both engines." |
•Architectural answer that stops at the query
- Recommends an index and a cluster key
- Suggests materialization without naming the refresh cadence
- Cannot articulate what happens on data skew
- Has no answer for retroactive updates
✓Architectural answer at the level the question rewards
- Maps the query to its supporting layer on both OLTP and OLAP engines
- Names the skew failure mode and the mitigation
- Recommends incremental refresh tied to the data change rate
- Articulates the operational concerns: corrections, deletes, partition drift
Distributed execution: the bottleneck is the shuffle
On any distributed engine (Spark, BigQuery, Snowflake, Presto), the dominant cost of this query is the shuffle that co-locates each department's rows on a single worker. The window function itself is cheap once the shuffle completes. If the source table is already partitioned or clustered by department_id, the shuffle is a no-op; if it is partitioned by date, the shuffle is the most expensive step in the plan. The architectural move is to align the source partitioning with the most common query partitioning. 'If this query runs frequently, I would partition the source by department_id; if other queries dominate, I would accept the shuffle cost for this query and optimize the others.' That tradeoff articulation is what gets the verdict written at the debrief.
Distributed execution checklist for top-N-per-group at scale:
- ▸Source partitioned or clustered by the partition column → shuffle is a no-op
- ▸Source partitioned by date → expensive shuffle on every refresh
- ▸One hot partition → single worker dominates; mitigate by splitting or materializing separately
- ▸Materialization with incremental refresh aligned to the data change rate
The closing summary
Close with a five-sentence wrap that walks the full architecture. 'The query is ROW_NUMBER partitioned by department with a tiebreaker chain that guarantees determinism. The function choice (ROW_NUMBER vs RANK vs DENSE_RANK) is driven by the consumer's question about ties. NULLS LAST and explicit NULL filtering handle the production data shape. The supporting layer is an index on OLTP or a cluster key on a warehouse; the materialization is a top-N-per-department table refreshed incrementally on the data's change cadence. The dominant runtime cost is the shuffle on department_id; aligning the source partitioning with the query partitioning is the lever that matters most at scale.' Five sentences. Each names a different architectural layer. The shape generalizes to every top-N-per-group question at this level.
❯❯❯PUTTING IT ALL TOGETHER
> You are in a Meta data engineering interview. The interviewer asks: 'You own the executive compensation dashboard. It shows top 3 highest-paid employees per department, refreshed hourly off a billion-row employees table. Walk me through your design.'
You frame the surface first: 'The dashboard reads from a top_3_per_dept materialized table, not from raw employees. The query I write is the body of the refresh job, not the user-facing query.'
You write the materialization query: ROW_NUMBER with a deterministic three-column ORDER BY, NULLS LAST, filtered to rn <= 3. You narrate why each ORDER BY column is there.
You name the supporting layer: 'On the warehouse side, the employees table is clustered by department_id; the refresh query is a partition-prune on the changed departments only, not a full scan.'
Follow-up: 'What happens when one department has 50 million employees and the rest have 50?' You say: 'Window functions cannot parallelize within a partition. The single hot department dominates the refresh time. The mitigation is either to materialize that department separately or to accept the long-running worker.'
Follow-up: 'How do you handle retroactive salary corrections?' You say: 'The refresh job re-runs the affected department's top-N computation. The materialized table is keyed on (department_id, refresh_timestamp); the dashboard pins to the latest valid version.'
KEY TAKEAWAYS
The correlated-subquery fallback's
OR cascade is the ORDER BY rewritten as a predicate, and its final clause must key on a unique column such as employee_id. Counting only strictly-greater salaries gives you RANK semantics, not exactly N rows per group.Pick the ranking function from the consumer's question about ties:
ROW_NUMBER for exactly N rows to act on, RANK for top N positions where ties consume extra rows, DENSE_RANK for top N distinct levels. The wrong pick is a quiet bug that surfaces when finance audits the bonus pool.On distributed engines, a
ROW_NUMBER whose ORDER BY does not break every tie is non-deterministic because parallel workers see tied rows in different orders; a query that is stable on single-node Postgres can return different results in production on Snowflake.Know which engine rewrites the correlated subquery into a hash operation. Postgres 12 and above do it, Postgres 11 and below execute it literally at O(N * D), and the self-join with a
HAVING COUNT(e2.employee_id) < 3 is the form to reach for where the rewrite does not happen.Top N per group is brutally sensitive to partition skew. Meta's ads-quality report timed out when one advertiser pushed the US to ~40% of the daily fact table and that partition serialized onto a single worker; the fix salted the hot key with
hash(advertiser_id) % 8 and materialized it separately.The dominant runtime cost on any distributed engine is the shuffle that co-locates a group's rows, so align the source layout with the query's partition key. Then materialize a top-N table refreshed incrementally on only the changed groups, and version it so retroactive salary corrections and soft deletes can be reprocessed without breaking the dashboard.
The single most common SQL interview question, hiding in plain sight
- Category
- SQL
- Difficulty
- advanced
- Duration
- 25 minutes
- Challenges
- 0 hands-on challenges
Topics covered: "Now Do It Without Window Functions", Correlated-Subquery Top-N Fallback, Top-N Performance and Index Strategy, Lateral / CROSS APPLY Top-N Per Row, Defending the Tie Rule on Performance
Lesson Sections
- "Now Do It Without Window Functions" (concepts: sqlRowNumber)
The question opens with 'top 3 highest-paid employees per department.' By the third follow-up, the conversation is about how to maintain that view continuously as the employees table changes, what happens when a region's headcount triples, and whether the right answer is a materialized view, a precomputed table, or an on-demand query. The SQL itself is settled in the first two minutes. The remaining time is about whether you can design the system that owns the SQL. What the interviewer is actual
- Correlated-Subquery Top-N Fallback (concepts: sqlSubqueryCorrelated)
At this level the interviewer often removes window functions from the table: 'now do it without ROW_NUMBER.' The move that earns the credit is not just producing a working correlated subquery; it is producing one whose tie-breaking condition is a deliberate contract, then pivoting the conversation to where the query actually lives in production: as a materialized table, refreshed on a cadence, fed by a partitioned source, consumed by a dashboard with a documented freshness SLA. The correlated-su
- Top-N Performance and Index Strategy (concepts: sqlRankDenseRank)
By this layer, the choice between ROW_NUMBER, RANK, and DENSE_RANK is not a syntax memory test. The interviewer is checking whether you can map the business question to the right function and articulate the failure modes of picking wrong. The function choice is a contract; the contract has consequences when ties exist in real data. The function-choice decision as a contract Each function answers a different question. ROW_NUMBER answers 'who are the N people we want to act on, exactly N, ties bro
- Lateral / CROSS APPLY Top-N Per Row (concepts: sqlSelfJoin)
At this level the no-window question becomes a probe for whether you understand the optimizer's behavior across engines. The two fallback forms are familiar: correlated subquery and self-join. The senior conversation is about which one the engine actually plans well and what it costs when the plan goes wrong. The correlated subquery plan, by engine On a modern engine, the correlated subquery 'WHERE (SELECT COUNT(*) FROM e2 WHERE ... ) < 3' is rewritten by the optimizer into a hash-based set oper
- Defending the Tie Rule on Performance (concepts: sqlRankDenseRank)
The closing layer is the architectural conversation. The interviewer asks how this scales to a billion rows, then how it scales to a billion rows with hourly refresh, then how it scales to a billion rows with hourly refresh on a distributed engine with skewed partition sizes. The answer is layered. Each layer adds a concept; each layer requires you to name a tradeoff. This is the section where the candidate's depth becomes visible. Layer 1 and 2: physical layout and data skew On an OLTP database