Rank Scores - Problem
Given a table Scores containing game scores, write a SQL query to rank the scores from highest to lowest.
Ranking Rules:
- Scores should be ranked from highest to lowest
- If there is a tie between two scores, both should have the same ranking
- After a tie, the next ranking number should be the next consecutive integer (no gaps in ranks)
Return the result table ordered by score in descending order.
Table Schema
Scores
| Column Name | Type | Description |
|---|---|---|
id
PK
|
int | Primary key, unique identifier for each game |
score
|
decimal | Game score with two decimal places |
Primary Key: id
Note: Each row represents a single game score
Input & Output
Example 1 — Basic Ranking with Ties
Input Table:
| id | score |
|---|---|
| 1 | 3.5 |
| 2 | 3.65 |
| 3 | 4 |
| 4 | 3.5 |
| 5 | 4 |
Output:
| score | rank |
|---|---|
| 4 | 1 |
| 4 | 1 |
| 3.65 | 2 |
| 3.5 | 3 |
| 3.5 | 3 |
💡 Note:
The highest score 4.00 appears twice and both get rank 1. The next unique score 3.65 gets rank 2 (not rank 3). The lowest score 3.50 appears twice and both get rank 3, demonstrating no gaps in ranking.
Example 2 — All Unique Scores
Input Table:
| id | score |
|---|---|
| 1 | 4.5 |
| 2 | 3.25 |
| 3 | 2.75 |
Output:
| score | rank |
|---|---|
| 4.5 | 1 |
| 3.25 | 2 |
| 2.75 | 3 |
💡 Note:
When all scores are unique, the ranking is straightforward with consecutive ranks 1, 2, 3 from highest to lowest score.
Example 3 — Single Score
Input Table:
| id | score |
|---|---|
| 1 | 5 |
Output:
| score | rank |
|---|---|
| 5 | 1 |
💡 Note:
With only one score in the table, it receives rank 1 as the highest (and only) score.
Constraints
-
1 ≤ id ≤ 1000 -
scoreis a decimal with two decimal places -
0.00 ≤ score ≤ 100.00
Visualization
Tap to expand
Understanding the Visualization
1
Input
Original scores table with id and score
2
DENSE_RANK
Window function assigns consecutive ranks
3
Output
Scores with ranks, ordered by score DESC
Key Takeaway
🎯 Key Insight: Use DENSE_RANK() when you need consecutive ranking numbers without gaps after ties
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code