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