Ranking & Top-N — ROW_NUMBER, RANK, DENSE_RANK, NTILE
Top-N per group is a senior interview staple. The wrong ranking function silently changes product behavior on ties. Compare ROW_NUMBER, RANK, DENSE_RANK, NTILE, and Postgres DISTINCT ON.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Top-1 per customer
Prefer
ROW_NUMBER plus a unique ORDER BY
Portable. Ties break on id. Top-k is the same pattern with rn less than or equal to k. Filter in an outer query.
- Retries agree because id is in ORDER BY.
- Works outside Postgres.
- Still sorts per partition. Read the plan on the EXPLAIN page if cost is the question.
Alternative
DISTINCT ON, or GROUP BY MAX then join
DISTINCT ON is a sharp Postgres Top-1. ORDER BY must start with the DISTINCT keys. MAX-then-join is familiar and ambiguous on ties.
- DISTINCT ON Top-N is awkward.
- MAX alone does not choose a full row.
- RANK equals 1 returns every tied winner.
Overview
Top-N per group is a senior interview staple. The wrong ranking function silently changes product behavior on ties.
| Function | Ties | Gaps after ties | Use |
|---|---|---|---|
| ROW_NUMBER | Unique ranks | None | Exact N rows, pagination, dedupe |
| RANK | Share a place | Yes | Leaderboards that share a place |
| DENSE_RANK | Share a place | No | Dense category places |
| NTILE(k) | Roughly equal buckets | Not a rank gap | Quartiles and strata. Approximate on small N |
Scores 100, 90, 90, 80:
RANKyields 1, 2, 2, 4.DENSE_RANKyields 1, 2, 2, 3.ROW_NUMBERyields 1, 2, 3, 4. Order among the 90s depends on the extraORDER BYkeys.
Flow
- 1
1 Partition and order
- next2 Assign the rank function
- 2
2 Assign the rank function
- next3 Filter in an outer query
- 3
3 Filter in an outer query
- next4 Return the Top-N set
- 4
4 Return the Top-N set
Lesson map
Ranking & Top-N — ROW_NUMBER, RANK, DENSE_RANK, NTILE
Top-N per group is a senior interview staple. The wrong ranking function silently changes product behavior on ties. Compare ROW_NUMBER, RANK, DENSE_RANK, NTILE, and Postgres DISTINCT ON.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB a["1 Partition and order"] b["2 Assign the rank function"] c["3 Filter in an outer query"] d["4 Return the Top-N set"] a -->|1 Partition and order| b b -->|2 Assign the rank function| c c -->|3 Filter in an outer query| d
Top-N per group
SELECT *
FROM (
SELECT
o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY amount DESC, created_at DESC, id DESC
) AS rn
FROM orders o
) t
WHERE rn <= 3;Always add a deterministic tie-break (id) so retries agree. Frame syntax itself is the window functions lesson. Ranking functions do not use a frame the way SUM does.
NTILE
SELECT user_id, revenue,
NTILE(4) OVER (ORDER BY revenue DESC) AS quartile
FROM user_revenue;NTILE buckets rows. percentile_cont estimates a value. They answer different questions. Small N makes the bucket sizes uneven.
Postgres DISTINCT ON
SELECT DISTINCT ON (customer_id)
customer_id, id, amount, created_at
FROM orders
ORDER BY customer_id, created_at DESC, id DESC;| Approach | Strength | Limit |
|---|---|---|
| ROW_NUMBER plus filter | Portable. Top-N is rn compared to k. | More verbose |
| DISTINCT ON | Idiomatic Postgres Top-1 | Non-standard. ORDER BY must start with the DISTINCT keys. Top-N is awkward |
| GROUP BY plus MAX join | Familiar | Tie ambiguity. Often two scans or joins |
Pick the function from the product rule
- 1
One row, any tie
ROW_NUMBER and a unique final key. - 2
Shared gold medal, skip the next place
RANK. 1, 2, 2, 4. - 3
Shared place, next integer is consecutive
DENSE_RANK. 1, 2, 2, 3. - 4
Four revenue bands
NTILE(4). Do not call it a percentile value. - 5
RANK = 1 as a single winner
Every tie is returned. That is not a single winner.
Sandboxes
Press Run. Snippets must be self-contained — no network, files, or native modules.
Press Run. Snippets must be self-contained — no network, files, or native modules.
The two 90s sort by id, so the row numbers are stable.
Deep dive · Sorts still happen
A partition ranking sorts. Whether that sort is cheap is a plan question. Read it on EXPLAIN and EXPLAIN ANALYZE. If only some outer rows need Top-N, compare LATERAL. Do not design the index on this page.
Pitfalls
Write the three sequences for scores 100, 90, 90, 80. Then write Top-3 per category with a unique tie-break, and the Postgres Top-1 DISTINCT ON form.
Interview Q&A
ROW_NUMBER vs RANK?
Answer
ROW_NUMBER is unique. RANK lets ties share a place and then skips.
When do you want DENSE_RANK?
Answer
When the product wants no gaps after ties. 1, 2, 2, 3 rather than 1, 2, 2, 4.
How do you take Top-3 per category?
Answer
ROW_NUMBER() partitioned by category, ordered by the metric plus a unique key, then rn <= 3 outside.
DISTINCT ON vs ROW_NUMBER?
Answer
DISTINCT ON for Top-1 in Postgres. ROW_NUMBER for portable Top-N. ORDER BY must lead with the DISTINCT ON expressions.
NTILE vs percentile_cont?
Answer
NTILE buckets rows. Continuous percentiles estimate values.
Why is Top-N unstable?
Answer
The ORDER BY is missing a deterministic key, so peers swap between runs.
Can I filter the rank in WHERE at the same level?
Answer
No. Wrap the query. Window functions run after WHERE.
Can RANK pick a single winner?
Answer
Not on ties. Use ROW_NUMBER or extra ORDER BY keys.
What about performance?
Answer
You still sort per partition. Supporting indexes can change the plan. Measure on EXPLAIN. This page stops at the ranking shape.