This is a LeetCode medium-level SQL problem, but if you have a strong understanding of window functions, this question will be quite easy for you. In this post, I will be solving this question using various approaches. So, let’s get started.
Rank Scores Question
Table: Scores
| Column Name | Type |
|---|---|
| id | int |
| score | decimal |
id is the primary key (column with unique values) for this table.
Each row of this table contains the score of a game. Score is a floating point value with two decimal places.
Write a solution to find the rank of the scores. The ranking should be calculated according to the following rules:
The scores should be ranked from the highest to the 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 value. In other words, there should be no holes between ranks.
Return the result table ordered by score in descending order.
The result format is in the following example.
Example 1:
Input:
Scores table:
| id | score |
|---|---|
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
Output:
| score | rank |
|---|---|
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
The Solutions
Solution 1: Dense Rank Approach
SELECT score, DENSE_RANK() OVER(ORDER BY score DESC) AS 'rank' FROM scores
Explanation – here we just have to use dense rank function on score column and order should be descending as per the question. Note. make sure this rank alias in single quotes or double quotes.
Solution 2: Subquery Approach
select s.Score, l.Rank
from Scores s
left join (
select Score, row_number() over (order by Score desc) as 'Rank'
from Scores
group by Score
) l on l.Score=s.Score
order by s.Score desc
Explanation:
Subquery – The subquery calculates the rank for each unique score in descending order using the ROW_NUMBER() function
Left Join and ordering score in descending – Now, let’s perform the left join between the original “Scores” table (aliased as s) and the subquery results (aliased as l) based on the “Score” column. now we will get desire output.
Conclusion
I would say this is very easy question as per medium level what do you think friends. hope you understood the my explanation. don’t forget to visit this website for such question. do practice every day.