178. Rank Scores

 

Rank Scores

Problem Description

SQL Schema

Create table If Not Exists Scores (id int, score DECIMAL(3,2))
Truncate table Scores
insert into Scores (id, score) values ('1', '3.5')
insert into Scores (id, score) values ('2', '3.65')
insert into Scores (id, score) values ('3', '4.0')
insert into Scores (id, score) values ('4', '3.85')
insert into Scores (id, score) values ('5', '4.0')
insert into Scores (id, score) values ('6', '3.65')

Table: Scores

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+

id is the primary key 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 an SQL query to rank 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 query 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    |
+-------+------+

Approach) Dense_Rank Approach​

• The problem states that there should be no holes between ranks so we're using Dense_Rank() to sort score from highest to lowest.
• rank is a reserved word in MySQL so we need to escape it with an apostrophe before and after the keyword in order to use it as a column name.

SELECT score,
    dense_rank() OVER (
        ORDER BY score DESC
        ) AS 'rank'
FROM Scores
ORDER BY score DESC

Approach) Subquery Approach​

• A subquery is used to find out how many distinct score values are greater than the current score value. Adding that value by 1 is the current score's rank.
• rank is a reserved word in MySQL so we need to escape it with an apostrophe before and after the keyword in order to use it as a column name.

SELECT s.score,
    (
        SELECT count(DISTINCT (ss.score)) + 1
        FROM Scores ss
        WHERE ss.score > s.score
        ) AS 'rank'
FROM Scores s
ORDER BY s.score DESC

Conclusion

That’s all folks! In this post, we solved LeetCode problem #178. Rank Scores

I hope you have enjoyed this post. Feel free to share your thoughts on this.

You can find the complete source code on my GitHub repository. If you like what you learn. feel free to fork 🔪 and star ⭐ it.

In this blog, I have tried to collect & present the most important points to consider when improving Data structure and logic, feel free to add, edit, comment, or ask. For more information please reach me here
Happy coding!