Rank Scores_leetcode #178

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.

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