Section 14: Window Functions (Deep Dive)
99
RANK vs DENSE_RANK vs ROW_NUMBER — predict the output with ties
Level: Hard — The single most-asked window-function question; the difference only shows up when values tie.
Scenario: The interviewer shows you a leaderboard with tied scores and asks: "Assign rank 1, 2, 3... Now, what does each of the three ranking functions output? And which one gives you a gap-free '1, 2, 3' vs a unique row number?" Predicting this correctly is the whole test.
Sample Data — scores
| player | score |
|---|
| Rumman | 100 |
| Krushna | 100 |
| Swetha | 90 |
| Ritesh | 90 |
| Manjula | 80 |
Predict the Output — Side by Side
| player | score | ROW_NUMBER | RANK | DENSE_RANK |
|---|
| Rumman | 100 | 1 | 1 | 1 |
| Krushna | 100 | 2 | 1 | 1 |
| Swetha | 90 | 3 | 3 | 2 |
| Ritesh | 90 | 4 | 3 | 2 |
| Manjula | 80 | 5 | 5 | 3 |
The Key Differences
- ROW_NUMBER(): Always unique, sequential — ignores ties, gives every row a distinct number (1,2,3,4,5).
- RANK(): Ties get the same rank, then it skips the next value(s) — creates gaps (1,1,3,3,5).
- DENSE_RANK(): Ties get the same rank, but no gaps — the next value is +1 (1,1,2,2,3).
Memory hook: ROW_NUMBER = "no ties allowed." RANK = "Olympic medals" (two golds → no silver, next is bronze/3rd). DENSE_RANK = "compact levels" (1,1,2,2,3 — no numbers skipped).
The Query
SELECT
player,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
RANK() OVER (ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM scores;
Which One to Use When
| Need | Use |
|---|
| Exactly one row per group (dedup, pick "latest") | ROW_NUMBER |
| Competition ranking with gaps (leaderboards) | RANK |
| "Top 3 distinct salary levels" / tier grouping | DENSE_RANK |
Common bug: Using RANK() to fetch "top 3 rows" can return more or fewer than 3 when ties exist (e.g., two 1sts + one 3rd = you skip 2nd). If you need exactly 3 rows, use ROW_NUMBER with a deterministic tie-breaker in the ORDER BY.
Interviewer follow-up: "You need exactly one 'winner' row per department for a payout — no ties allowed. Which function, and how do you guarantee determinism?" → ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY score DESC, player_id). The extra player_id tie-breaker makes the result deterministic and repeatable even when scores tie.