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, with 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.