RANK and DENSE_RANK difference in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Difference in output between RANK() and DENSE_RANK()

Consider the following table Scores with columns Player and Points:

Player | Points
-------|-------
Alice  | 100
Bob    | 90
Carol  | 90
Dave   | 80

What is the output of the following query?

SELECT Player, Points, RANK() OVER (ORDER BY Points DESC) AS RankPos
FROM Scores;

SQL

CREATE TABLE Scores (Player VARCHAR(10), Points INT);
INSERT INTO Scores VALUES
('Alice', 100), ('Bob', 90), ('Carol', 90), ('Dave', 80);

SELECT Player, Points, RANK() OVER (ORDER BY Points DESC) AS RankPos
FROM Scores;

A

Alice 100 1
Bob 90 3
Carol 90 3
Dave 80 4

B

Alice 100 1
Bob 90 2
Carol 90 2
Dave 80 4

C

Alice 100 1
Bob 90 2
Carol 90 2
Dave 80 3

D

Alice 100 1
Bob 90 2
Carol 90 3
Dave 80 4

💡 Hint

RANK() assigns the same rank to ties but skips ranks after ties.

RANK() assigns the same rank to tied rows and then skips the next ranks accordingly. Here, Bob and Carol tie at 90 points and share rank 2, so the next rank is 4 for Dave.

❓ query_result

intermediate

Output of DENSE_RANK() with ties

Using the same Scores table, what is the output of this query?

SELECT Player, Points, DENSE_RANK() OVER (ORDER BY Points DESC) AS DenseRankPos
FROM Scores;

SQL

SELECT Player, Points, DENSE_RANK() OVER (ORDER BY Points DESC) AS DenseRankPos
FROM Scores;

A

Alice 100 1
Bob 90 2
Carol 90 3
Dave 80 4

B

Alice 100 1
Bob 90 3
Carol 90 3
Dave 80 4

C

Alice 100 1
Bob 90 2
Carol 90 2
Dave 80 3

D

Alice 100 1
Bob 90 2
Carol 90 2
Dave 80 4

💡 Hint

DENSE_RANK() assigns the same rank to ties but does not skip ranks after ties.

DENSE_RANK() assigns the same rank to tied rows but the next rank is the immediate next integer. So Bob and Carol share rank 2, and Dave gets rank 3.

🧠 Conceptual

advanced

Understanding rank gaps with RANK()

Why does the RANK() function sometimes skip rank numbers after ties?

A Because RANK() counts the number of tied rows and skips ranks accordingly to reflect the position in the ordered list.

B Because RANK() always assigns consecutive ranks without gaps regardless of ties.

C Because RANK() assigns random ranks to tied rows causing gaps.

D Because RANK() ignores ties and assigns unique ranks to each row.

💡 Hint

Think about how RANK() reflects the position of rows in the sorted order.

RANK() assigns the same rank to tied rows but then skips the next ranks to reflect the actual position in the sorted list. For example, if two rows tie at rank 2, the next rank is 4, skipping 3.

🧠 Conceptual

advanced

When to use DENSE_RANK() instead of RANK()

Which scenario best describes when to use DENSE_RANK() instead of RANK()?

A When you want to assign ranks without gaps even if there are ties, such as ranking products by sales where consecutive ranks are needed.

B When you want to skip ranks after ties to reflect the actual position in the list.

C When you want to assign random ranks to rows.

D When you want to ignore ties and assign unique ranks.

💡 Hint

Consider if you want ranks to be consecutive numbers or to reflect gaps.

DENSE_RANK() is used when you want ranks to be consecutive numbers without gaps, even if there are ties. This is useful when you want a compact ranking sequence.

📝 Syntax

expert

Identify the syntax error in RANK() usage

Which of the following SQL queries will cause a syntax error?

SELECT Player, Points, RANK(ORDER BY Points DESC) AS RankPos FROM Scores;

A SELECT Player, Points, RANK() OVER (PARTITION BY Player ORDER BY Points DESC) AS RankPos FROM Scores;

B SELECT Player, Points, RANK() OVER (ORDER BY Points DESC) AS RankPos FROM Scores;

C SELECT Player, Points, RANK() OVER (ORDER BY Points DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RankPos FROM Scores;

D SELECT Player, Points, RANK(ORDER BY Points DESC) AS RankPos FROM Scores;

💡 Hint

Check the correct syntax for window functions like RANK().

RANK() requires the OVER clause with parentheses. Option D misses OVER and will cause a syntax error.