✏️ Explanatory Question
Level: Coding Round — A LeetCode classic; the requirement "ties share a rank, next rank is consecutive" points straight to DENSE_RANK.
The Puzzle: Given a scores table, rank the scores from highest to lowest. If two scores are equal they get the same rank, and after tied scores the next rank must be the immediately consecutive number (no gaps). Order the result by score descending.
scores| id | score |
|---|---|
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
| score | rank |
|---|---|
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
Both 4.00 scores are rank 1; the next score (3.85) is rank 2 — NOT rank 3. That "no gap" requirement is the key clue.
The decisive clue — DENSE_RANK: The phrase "next rank is the next consecutive integer (no gaps)" points directly to DENSE_RANK(). Plain RANK() would skip to rank 3 after two 1st-place ties (1,1,3), which violates the requirement. DENSE_RANK gives 1,1,2 — exactly what is asked.
SELECT
score,
DENSE_RANK() OVER (ORDER BY score DESC) AS 'rank'
FROM scores
ORDER BY score DESC; Note the backticks: rank became a reserved keyword in MySQL 8.0 (because of the RANK window function). If you name a column rank, you must wrap it in backticks (`rank`) or you get a syntax error. A subtle gotcha that trips people up in this exact problem.
-- Rank = number of DISTINCT scores greater than or equal to this score
SELECT s.score,
(SELECT COUNT(DISTINCT s2.score)
FROM scores s2
WHERE s2.score >= s.score) AS 'rank'
FROM scores s
ORDER BY s.score DESC; How Solution 2 mimics DENSE_RANK: Counting distinct scores that are >= the current score naturally produces gap-free ranks. The top score has 1 distinct score at-or-above it (rank 1); a tie shares that count; the next lower score has 2, and so on. The DISTINCT is what makes it "dense."
| Scores | RANK() | DENSE_RANK() (correct) |
|---|---|---|
| 4.00, 4.00, 3.85 | 1, 1, 3 | 1, 1, 2 |
Interviewer follow-up: "What if the leaderboard is per game/category, not global?" → Add PARTITION BY game_id inside the OVER clause: DENSE_RANK() OVER (PARTITION BY game_id ORDER BY score DESC). Each game gets its own independent 1,2,3 ranking — the partition restarts the ranking per group, which is the standard extension for grouped leaderboards.