Rank Scores
Meta
Problem Statement
Leaderboards at Meta must be fair and intuitive. When ranking scores, if two scores are identical, they should share the same ranking position. Crucially, the next ranking number after a tie should be the next consecutive integer (i.e., there should be no holes in the sequence).
Write an SQL query to rank the scores.
Rules:
- Scores should be ranked from highest to lowest.
- If two scores are equal, both should have the same rank.
- After a tie, the next ranking integer should be the next consecutive value.
- Return the columns: score and rank.
- Results must be sorted by score in descending order.
Tests your understanding of
Basic SQL, Window Functions, Ranking and DENSE_RANK
Input Tables
scores
| id(INTEGER) | score(DECIMAL) |
|---|---|
| 1 | 3.5 |
| 2 | 3.65 |
| 3 | 4 |
| 4 | 3.85 |
| 5 | 4 |
| 6 | 3.65 |
Expected Output
| score(DECIMAL) | rank(INTEGER) |
|---|---|
| 4 | 1 |
| 4 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.5 | 4 |
Tags
MediumBasic SQLWindow FunctionsRankingDENSE_RANK
15-20 min
60%