🧑‍💼 Interviewer: What's the difference between RANK() and DENSE_RANK() in SQL?

👨‍💻 Me: Here's a quick example using salaries:

SELECT name, department, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank_salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_salary
FROM employees;

✔ Key Differences:
– RANK(): Skips ranks if there's a tie (e.g., two at #1, next is #3)—great for competitions where gaps show true position.
– DENSE_RANK(): No gaps—ranks increase sequentially (e.g., two at #1, next is #2)—ideal for leaderboards or tiers without skips.

📌 Example:
If two people tie at 1st place:
⦁ RANK() → 1, 1, 3
⦁ DENSE_RANK() → 1, 1, 2

💡 Use DENSE_RANK() when you want consistent rank steps, like in sales reports—add PARTITION BY department for per-group ranking!

💬 Tap ❤️ for more!