SQL Window Functions Problem Solution

Rank Scores via Leetcode

In extreme detail, in this tutorial I'll walk through a Leetcode medium level SQL problem to arrive to the solution.

Problem Statement via Leetcode

Write a SQL query to rank scores. 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.

Id Score
1 3.50
2 3.65
3 4.00
4 3.85
5 4.00
6 3.65

For example, given the above Scores table, your query should generate the following report (order by highest score):

Score Rank
4.00 1
4.00 1
3.85 2
3.65 3
3.65 3
3.50 4

Dan's Step by Step Solution

Use DENSE_RANK() to Get Ranking of Scores in Descending Order

Explanation of SQL Query Below:

I create a new column using the DENSE_RANK() method that is applied over all rows and Score values and the rows are ordered by Score from greatest to least.

The DENSE_RANK() method doesn't skip any rank numeric values. Any numeric values of the same size will get the same rank value.

SQL Query:

SELECT Score AS "Score",
       DENSE_RANK() OVER (ORDER BY Score DESC) AS "Rank"
FROM Scores

Returned Output:

Score Rank
4.00 1
4.00 1
3.85 2
3.65 3
3.65 3
3.50 4