Rank Scores_leetcode #178

Question

Could someone help explain how to solve this question? I am a beginner of sql and don't know how to use variables.

Write a SQL query to rank scores given Scores table. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks.

Question Description

https://leetcode.com/problems/rank-scores/description/

I have looked the solutions in the discussion forum but still can't understand the logic behind it. It would be greatly appreciated if someone can provide step by step explanations. One of the possible solutions is like (without variables):

select scores.Score, count(ranking.Score) as Rank
from scores, (select distinct Score from scores) ranking
where ranking.score>=scores.Score
group by scores.Id
order by scores.Score desc

Thanks!

Answer 1

Let's start with having a look at the example of expected input and output:

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

So, the task is to group all identical scores and then order them from largest to smallest. Let's see how the solution you mentioned achieves it, step by step. First, it creates a helper table called ranking - note (select distinct Score from scores) ranking. Its content will be:

+----+--+
| Score |
+----+--+
| 3.50  |
| 3.65  |
| 4.00  |
| 3.85  |        
+----+--+

Notice how all duplicate scores have been eliminated (that is the purpose of distinct keyword). Next, there is a join between tables ranking and scores (hidden in where part) where we join each record from scores table with all records from ranking table that have greater or equal score. So, the result of this mid-phase would be:

+----+-------+---------+
| Id | Score | r.Score |
+----+-------+---------+
| 1  | 3.50  | 3.50    |
| 1  | 3.50  | 3.65    |
| 1  | 3.50  | 3.85    |
| 1  | 3.50  | 4.00    |
| 2  | 3.65  | 3.65    |
| 2  | 3.65  | 3.85    |
| 2  | 3.65  | 4.00    |
| 3  | 4.00  | 4.00    |
| 4  | 3.85  | 3.85    |
| 4  | 3.85  | 4.00    |
| 5  | 4.00  | 4.00    |
| 6  | 3.65  | 3.65    |
| 6  | 3.65  | 3.85    |
| 6  | 3.65  | 4.00    |
+----+-------+---------+

Next comes group by which groups all records with same Id into one record. Since in the select part we have count(ranking.Score), the result of grouping will be the count of different ranking scores for each Id. And since we joined from ranking only those scores that are greater or equal than the original score, this count will give the requested ranking. We are almost done:

+----+-------+--------+-------+
| Id | count(r.Score) | Score |
+----+-------+--------+-------+
| 1  |       4        | 3.50  |
| 2  |       3        | 3.65  |
| 3  |       1        | 4.00  |
| 4  |       2        | 3.85  |
| 5  |       1        | 4.00  |
| 6  |       3        | 3.65  |
+----+-------+--------+-------+

Now the easiest part - order by which orders the results by score. Since the select does not include Id, that column is omitted and we get the final result. Hope this helps!

P.S. Because we are using MySQL, we can omit scores.Score from group by part and still use it in select - this is not allowed in other SQL engines. You indicated that you are beginner so don't worry much about this, just mentioning it for the completeness.

Answer 2

select S.Score, Dense_Rank() over(order by S.Score desc) 'Rank' from Scores S