Explain the differences between ROW_NUMBER(), RANK(), and DENSE_RANK()

Side-by-side example

SELECT
    name, score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK()       OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM contestants;

• ROW_NUMBER(): Bob and Carol are tied at 90, but get distinct numbers (2, 3) anyway — the tie is broken arbitrarily (or deterministically, if you add more ORDER BY columns to fully disambiguate). No two rows ever share a ROW_NUMBER().
• RANK(): Bob and Carol both get rank 2 (tied), and Dave — the next distinct value — gets rank 4, skipping 3 entirely, because two rows already "used up" ranks 2 and 3.
• DENSE_RANK(): Bob and Carol both get rank 2, and Dave gets rank 3 — no gap, because dense rank only increments for each distinct value encountered, not for each row.

When to use which

• ROW_NUMBER(): when you need a strictly unique sequential identifier per row regardless of ties — e.g., picking exactly one "first" row per group (deduplication, pagination, "keep only the latest record per customer").
• RANK(): when ties should share a rank, and you want the rank to reflect "how many rows are strictly better," including the tie count — e.g., a leaderboard where two people tied for 2nd place means the next person really is in "4th place" by count.
• DENSE_RANK(): when ties should share a rank, but you want ranks to be a compact, gapless sequence — e.g., "top 3 distinct price tiers," where you care about distinct values, not row counts.

A common use: "top N per group"

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) ranked
WHERE rn <= 3;   -- top 3 highest-paid employees PER department

This pattern (ROW_NUMBER() + PARTITION BY + filter in an outer query) is one of the most common real-world uses of window functions, and a frequent live-coding interview exercise.