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.
For example, given the above Scores table, your query should generate the following report (order by highest score):
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.
DENSE_RANK() method doesn't skip any rank numeric values. Any numeric values of the same size will get the same rank value.
SELECT Score AS "Score", DENSE_RANK() OVER (ORDER BY Score DESC) AS "Rank" FROM Scores