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.