SQL Medium Difficulty Problem : Rank Scores

Leetcode Problem No. 178 

Hello All,

We are back with another SQL problem to gain understanding about SQL. This blog-post focuses on SQL window functions, understanding difference between 2 SQL functions RANK() and DENSE_RANK().

Give a try to solve below problem. Solution is provided as usual. Feel free to comment if any queries and also suggest alternate approach to solve this problem. Have a Happy Learning !  

178. Rank Scores

SQL Schema

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    |
+-------+------+

Solution :

SELECT score ,  #do not forget comma(,) here 

dense_rank() over(

                                Order by score desc

                               ) as 'rank'

FROM Scores

Explanation :

Here, in the solution, 3 important things are used :

1. DENSE_RANK()

2. OVER clause

3. ORDER BY

             The dense rank is a window function and it need to supported by OVER clause. To know more about window functions and OVER clause use following citations.

1. Guide to Window functions  and how it works : An Easy Guide to Advanced SQL Window Functions | by Julia Kho | Towards Data Science 

2. Window functions in SQL - GeeksforGeeks

Now, there are two functions often used to rank : DENSE_RANK() and RANK()

 ---------------------------------------------------------------------------------------------------------------

Output when DENSE_RANK() is used                  vs                 Ouput when RANK() is used

ORDER BY : It orders the column either ascending or descending. If nothing is mentioned it orders

                       default ascending. 

                        To order descending use clause ORDER BY DESC