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.

Rating: 1 out of 5.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s