RANK vs DENSE_RANK in Leaderboards

Context

Ranking functions are common in reporting, leaderboards, and top-N analysis. Interviewers ask this question to check whether you understand how SQL handles ties in ordered results.

Core Question

Explain the difference between RANK() and DENSE_RANK() in SQL. Your answer should cover:

1. How each function assigns ranks when multiple rows have the same ordering value
2. Why RANK() can create gaps in rank numbers
3. Why DENSE_RANK() does not create gaps
4. When you would choose one over the other in a real query

Scope Guidance

Keep the explanation practical. Define both functions clearly, compare their outputs on the same example, and mention common use cases such as competition standings, sales rankings, or grouped reporting. You do not need to discuss advanced execution details, but you should be precise about tie behavior and the impact on downstream filtering like top 3 or top 10 results.

RANK()

RANK() assigns the same rank to tied rows, then skips the next rank values based on how many rows were tied. This means the ranking sequence can contain gaps.

SELECT employee_name, sales_amount, RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM sales_results;

DENSE_RANK()

DENSE_RANK() also assigns the same rank to tied rows, but it does not skip subsequent rank values. The ranking sequence remains consecutive.

SELECT employee_name, sales_amount, DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM sales_results;

Tie Handling

Both functions treat rows with identical ORDER BY values as ties and assign them the same rank. The difference appears only in the next distinct value after the tie.

SELECT employee_name, sales_amount,
       RANK() OVER (ORDER BY sales_amount DESC) AS rank_value,
       DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS dense_rank_value
FROM sales_results;

Business Interpretation

RANK() matches competition-style standings, where if two people tie for second, the next person is fourth. DENSE_RANK() is better when you want distinct value levels numbered without gaps.