Recap — Top-N Without Falling Into the Trap

# Top-N Without Falling Into the Trap The setup: *find the top 3 products by revenue in each region*. Every analyst has been asked this. The query template is: ```sql WITH ranked AS ( SELECT region, product, revenue, ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rn FROM product_revenue ) SELECT * FROM ranked WHERE rn <= 3; ``` The trick: there are three window functions you could use here, and they give different answers when there are ties. ## ROW_NUMBER vs RANK vs DENSE_RANK Suppose three products in a region all have revenue = ₹10,000: | Product | Revenue | ROW_NUMBER | RANK | DENSE_RANK | |---|---|---|---|---| | A | 10,000 | 1 | 1 | 1 | | B | 10,000 | 2 | 1 | 1 | | C | 10,000 | 3 | 1 | 1 | | D | 9,000 | 4 | 4 | 2 | | E | 8,000 | 5 | 5 | 3 | - **ROW_NUMBER** — arbitrary tiebreak (often by insertion order). Always returns distinct integers. - **RANK** — same rank for ties, then a gap. Three at rank 1, next is rank 4. - **DENSE_RANK** — same rank for ties, no gap. Three at rank 1, next is rank 2. ## Which one does the interviewer want? It depends on the question: - *"Give me 3 rows per region"* → `ROW_NUMBER() <= 3`. Returns exactly 3 even with ties (arbitrary tiebreak). - *"Give me the top 3 products including ties"* → `DENSE_RANK() <= 3`. Could return more than 3 rows if there are ties. - *"Top 3 by rank, with gaps for ties"* → `RANK() <= 3`. Returns ties at the same rank; if 3 products tie for #1, only those 3 come back (no #4). **The most common interview wrong answer**: using `ROW_NUMBER` when the question said "top 3 including ties". You'll silently drop tied products. ## The deterministic-tiebreak trap `ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC)` doesn't guarantee deterministic ordering for ties. If you re-run the query you could get different rows back. The fix: ```sql ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC, product_id ASC) ``` Add a tiebreaker column. Now ties resolve deterministically. ## The N+1 trap The classic interview follow-up: *"What if N is a parameter?"* The right SQL: ```sql WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rn FROM product_revenue ) SELECT * FROM ranked WHERE rn <= :n; ``` What you should avoid: `LIMIT :n` per region — there's no `LIMIT PER GROUP` in standard SQL. Some databases (PostgreSQL) have `LATERAL` joins for this; others (MySQL) don't. Window functions are portable. ## Performance note For very large tables with cheap indexes, `LATERAL` joins can beat window functions: ```sql SELECT r.region, p.* FROM regions r JOIN LATERAL ( SELECT * FROM product_revenue pr WHERE pr.region = r.region ORDER BY pr.revenue DESC LIMIT 3 ) p ON TRUE; ``` But it's PostgreSQL-only. In interviews, default to window functions unless asked. ## Takeaway Listen to the question. *"Top 3"* with ties means DENSE_RANK or RANK; without ties means ROW_NUMBER. Always ask the interviewer how to handle ties — it shows judgment.