Yahoo SQL interview Questions – Leetcode 178 – Rank Scores

Problem Description –

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).

Important Note: For MySQL solutions, to escape reserved words used as column names, you can use an apostrophe before and after the keyword. For example `Rank`.

Difficulty Level – Medium

Solution –

SELECT
    score,
    DENSE_RANK() OVER(ORDER BY score DESC) as "Rank"
FROM scores

In this problem, It is mentioned 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. Which basically is saying to you to use the DENSE_RANK() function instead of the RANK() function.

When you use DENSE_RANK() function, in case of a tie it doesn’t leaves a hole which can be seen below.

But if I use RANK() function, in case of a tie it will leaves holes in ranking which can be seen below.

SELECT
    score,
    RANK() OVER(ORDER BY score DESC) as "Rank"
FROM scores

You can see that after the second row, we have the rank of 3 instead of 2.