What's the difference between RANK, DENSE_RANK and ROW_NUMBER, and when does the choice actually matter?

TL;DR: All three number rows within a partition and differ only on ties: ROW_NUMBER forces a unique arbitrary order, RANK shares the tied rank then skips, DENSE_RANK shares it with no gap. The choice silently corrupts top-N and Nth-value queries when ties exist, and ROW_NUMBER without a tiebreaker is non-deterministic across reruns.

How to approach it

State the difference in one breath, then show you know when each one is the right tool, that's what separates a working data person from someone who memorized a flashcard. Anchor it with a tiny example involving ties, because ties are the entire point.

A strong answer

All three are window functions that number rows within a partition by some ordering. They diverge only on ties:

• ROW_NUMBER(), arbitrary unique numbering: 1, 2, 3, 4 even if rows 2 and 3 tie. Non-deterministic across ties unless you add a tiebreaker column to the ORDER BY.
• RANK(), ties share a rank, and the next rank skips: 1, 2, 2, 4.
• DENSE_RANK(), ties share a rank, no gaps: 1, 2, 2, 3.

SELECT
  salesperson,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS rn,
  RANK()       OVER (ORDER BY revenue DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS drnk
FROM sales;

When the choice matters:

• Top-N per group: use ROW_NUMBER() if you need exactly N rows (pagination, "pick one winner"); use RANK() or DENSE_RANK() if ties should all qualify ("top 3 scores" where four people tie for third should arguably return more rows).
• Nth-highest value: use DENSE_RANK(). With RANK(), the gap after ties means "rank = 3" may not exist; with ROW_NUMBER(), duplicates of the same salary occupy multiple positions, so "3rd row" isn't "3rd-highest value."
• Deduplication: ROW_NUMBER() is the standard idiom, partition by the business key, order by updated_at DESC, keep rn = 1.

The silent corruption is worth seeing on five rows, because it never announces itself. Sales has Priya and Marcus both at 120,000; Ops has Dana at 88,000. Run "top earner per department" both ways (executed, output shown):

-- ROW_NUMBER() ... WHERE rn = 1 returns:   Dana | Priya
-- RANK()       ... WHERE rnk = 1 returns:  Dana | Priya | Marcus

The ROW_NUMBER version quietly dropped Marcus, a legitimately tied top earner, and nothing failed: no error, no warning, a plausible-looking result set one row short. If that query feeds a bonus calculation, Marcus's missing bonus is discovered by Marcus. Worse, which of the two survives is arbitrary, so the report can name Priya this run and Marcus after tomorrow's re-run. That pair of behaviors, wrong quietly and differently each time, is why interviewers press on ties: it is the shape of bug that reaches production and stays there.

One sentence on determinism lands well: "If I use ROW_NUMBER for dedupe, I always add a deterministic tiebreaker like the primary key to the ORDER BY, otherwise reruns can keep different rows, which breaks idempotency downstream."

What interviewers probe next

• "Write top-3 products per region", the natural follow-up; know the CTE + QUALIFY (Snowflake/Databricks) pattern: QUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) <= 3.
• "Why can't you put a window function in WHERE?", logical evaluation order: windows compute after WHERE/GROUP BY/HAVING, hence the subquery/CTE or QUALIFY requirement.
• "What does NTILE do?" or "PERCENT_RANK?", bucketing and relative position; nice-to-have.
• Performance: a window over a huge unpartitioned dataset forces a global sort; partitioning bounds the sort.

Common mistakes

• Stating the tie behavior backwards (RANK vs DENSE_RANK). Sketch 1,2,2,4 vs 1,2,2,3 to keep it straight.
• Using ROW_NUMBER() for "Nth-highest salary" and getting burned by duplicate salaries.
• Filtering on the window alias in the same SELECT's WHERE clause, syntax error candidates write under pressure constantly.
• Forgetting PARTITION BY entirely when the question says "per department/region," producing one global ranking.

Key takeaways

• Tie behavior is the whole question: 1,2,2,4 (RANK) versus 1,2,2,3 (DENSE_RANK) versus 1,2,3,4 (ROW_NUMBER).
• DENSE_RANK for Nth-distinct-value, ROW_NUMBER for dedupe and exactly-N, RANK or DENSE_RANK when ties should all qualify.
• ROW_NUMBER for dedupe is only idempotent if the ORDER BY ends in a unique column.