Recap — 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:

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:

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:

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:

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.