RANK vs DENSE_RANK: Tie-Traps
Top-3-per-group with ties: ROW_NUMBER vs RANK vs DENSE_RANK explained side by side, plus 5 tie-trap drills with full answer keys for interviews.
Top 3 spenders per country. Ties everywhere. Three functions, three different answers — and interviewers plant ties deliberately.
The three behaviors
- ROW_NUMBER gives 1,2,3 no matter what. Ties get split arbitrarily.
- RANK gives 1,2,2,4. Honest about ties, but now there is no rank 3.
- DENSE_RANK gives 1,2,2,3. Ties honored, top 3 means top 3.
5 drills (answers below)
- Top 3 salaries per department, 2-way tie at rank 2. What does each function return, and which is "top 3"?
- DENSE_RANK over values where 5 rows tie for 1st — how many rows are in "top 3"?
- RANK leaderboard shows 1,2,2,4. Product wants exactly 3 rows. Which function, and what tradeoff do you disclose?
- ROW_NUMBER partitioned by month for latest order per customer — why do you need a second ORDER BY column?
- Deduplicate to one row per customer keeping max order_date — ROW_NUMBER or RANK, and what breaks with ties?
Answer key
- ROW_NUMBER = 3 rows (arbitrary cut), RANK = 4 rows. "Top 3" is ambiguous — ask what should happen to ties.
- 5+ rows — every row with rank ≤ 3 qualifies.
- DENSE_RANK, and disclose that ties inflate the row count.
- Without a tie-breaker the pick among tied rows is nondeterministic — add order_id.
- ROW_NUMBER; RANK keeps all tied rows, so duplicates survive.
The syntax is commodity. Asking "what should happen to ties?" before writing the query is what signals senior thinking.
Practice window functions on messy data at Topfolio Practice.
Related: Sql Window Function Traps · Sql 30 Interview Questions Practice
Frequently Asked Questions
When should I use DENSE_RANK over RANK?
When the business needs exactly the top N distinct levels (e.g. top 3 tiers) with ties included. RANK skips numbers after ties, so 'top 3' can return 2 or 4+ rows.
Why is ROW_NUMBER dangerous with ties?
It assigns 1,2,3 arbitrarily among tied rows, so the cutoff is nondeterministic unless you add a second ORDER BY column as tie-breaker.
Do interviewers really plant ties?
Yes — tied values are the standard way to test whether you ask about business rules before writing the query.
Written by
Founder at Topfolio with 6+ years in data & analytics across JPMC, Ultrahuman, and high-growth startups. Sat on hiring panels, reviewed 500+ resumes, and writes practical SQL & data guides.
Related Articles
5 Window Traps That Lie
Basket-split LAG gaps, ghost orders, nondeterministic ROW_NUMBER, RANK gaps, unframed running totals: 5 window traps with fixes and SQL included.
30 SQL Interview Questions, Free
The 30 SQL patterns analyst interviews repeat — JOINs, windows, GROUP BY traps, NULLs, dates, CTEs — indexed by pattern with live practice links.
4 GROUP BY Traps, Wrong vs Right
WHERE vs HAVING, nullable grouping keys, bare SELECT columns: 4 GROUP BY traps that run without errors yet fail take-homes, each with the fix.