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.

Practice

1. What is the main difference between RANK() and DENSE_RANK() functions in SQL?

easy

A. DENSE_RANK() assigns random ranks, RANK() assigns sequential ranks.

B. RANK() skips rank numbers after ties, DENSE_RANK() does not skip numbers.

C. RANK() and DENSE_RANK() always return the same results.

D. DENSE_RANK() skips rank numbers after ties, RANK() does not skip numbers.

Solution

1. Step 1: Understand how RANK() works with ties

   RANK() assigns the same rank to tied rows but skips the next rank numbers accordingly. For example, if two rows tie for rank 1, the next rank will be 3.

2. Step 2: Understand how DENSE_RANK() works with ties

   DENSE_RANK() also assigns the same rank to tied rows but does not skip rank numbers. So after two rows tied at rank 1, the next rank will be 2.

3. Final Answer:

   RANK() skips rank numbers after ties, DENSE_RANK() does not skip numbers. -> Option B

4. Quick Check:

   Skipping ranks = RANK() [OK]

Hint: Remember: RANK skips numbers after ties, DENSE_RANK does not [OK]

Common Mistakes:

• Thinking both functions behave the same with ties
• Confusing which function skips rank numbers
• Assuming ranks are assigned randomly

2. Which of the following is the correct syntax to get the rank of employees ordered by salary using DENSE_RANK()?

easy

A. SELECT employee_id, RANK() BY salary FROM employees;

B. SELECT employee_id, DENSE_RANK(salary) FROM employees ORDER BY salary DESC;

C. SELECT employee_id, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;

D. SELECT employee_id, DENSE_RANK() ORDER BY salary FROM employees;

Solution

1. Step 1: Identify correct DENSE_RANK() syntax

   The correct syntax uses DENSE_RANK() OVER (ORDER BY column) to rank rows based on a column.

2. Step 2: Check each option

   SELECT employee_id, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM employees; uses the correct window function syntax. Options A, B, and D have syntax errors or misuse the function.

3. Final Answer:

   SELECT employee_id, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM employees; -> Option C

4. Quick Check:

   Window function syntax = SELECT employee_id, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM employees; [OK]

Hint: Use OVER (ORDER BY ...) with ranking functions [OK]

Common Mistakes:

• Omitting OVER clause
• Passing column inside function parentheses
• Using wrong keywords like BY instead of ORDER BY

3. Given the table Scores with values:
Player | Score
Alice | 100
Bob | 90
Carol | 90
Dave | 80
What will be the output of this query?

SELECT Player, Score, RANK() OVER (ORDER BY Score DESC) AS Rank, DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank FROM Scores;

medium

A. [{"Player":"Alice","Score":100,"Rank":1,"DenseRank":1},{"Player":"Bob","Score":90,"Rank":2,"DenseRank":2},{"Player":"Carol","Score":90,"Rank":2,"DenseRank":2},{"Player":"Dave","Score":80,"Rank":4,"DenseRank":3}]

B. [{"Player":"Alice","Score":100,"Rank":1,"DenseRank":1},{"Player":"Bob","Score":90,"Rank":3,"DenseRank":2},{"Player":"Carol","Score":90,"Rank":3,"DenseRank":2},{"Player":"Dave","Score":80,"Rank":4,"DenseRank":3}]

C. [{"Player":"Alice","Score":100,"Rank":1,"DenseRank":1},{"Player":"Bob","Score":90,"Rank":2,"DenseRank":3},{"Player":"Carol","Score":90,"Rank":2,"DenseRank":3},{"Player":"Dave","Score":80,"Rank":3,"DenseRank":4}]

D. [{"Player":"Alice","Score":100,"Rank":1,"DenseRank":1},{"Player":"Bob","Score":90,"Rank":2,"DenseRank":2},{"Player":"Carol","Score":90,"Rank":3,"DenseRank":2},{"Player":"Dave","Score":80,"Rank":4,"DenseRank":3}]

Solution

1. Step 1: Assign RANK() values

   Alice has highest score 100, rank 1. Bob and Carol tie at 90, both get rank 2. Because of tie at rank 2, next rank is 4 for Dave.

2. Step 2: Assign DENSE_RANK() values

   Alice is dense rank 1. Bob and Carol tie at 90, dense rank 2. Dave has next dense rank 3 (no skipping).

3. Final Answer:

   [{"Player":"Alice","Score":100,"Rank":1,"DenseRank":1},{"Player":"Bob","Score":90,"Rank":2,"DenseRank":2},{"Player":"Carol","Score":90,"Rank":2,"DenseRank":2},{"Player":"Dave","Score":80,"Rank":4,"DenseRank":3}] -> Option A

4. Quick Check:

   RANK skips numbers, DENSE_RANK does not [OK]

Hint: Check how ranks jump after ties for RANK vs DENSE_RANK [OK]

Common Mistakes:

• Assigning dense ranks with gaps
• Not skipping ranks in RANK after ties
• Mixing up rank numbers for tied rows

4. You wrote this query:

SELECT name, RANK() ORDER BY score DESC FROM players;

But it gives a syntax error. What is the problem?

medium

A. Missing OVER() clause after RANK() function.

B. ORDER BY cannot be used inside SELECT clause.

C. RANK() requires a parameter inside parentheses.

D. The table name is missing in the query.

Solution

1. Step 1: Check RANK() function usage

   RANK() is a window function and must be followed by OVER() clause specifying order.

2. Step 2: Identify missing OVER() clause

   The query uses RANK() without OVER(), causing syntax error.

3. Final Answer:

   Missing OVER() clause after RANK() function. -> Option A

4. Quick Check:

   Window functions need OVER() [OK]

Hint: Always add OVER() after window functions like RANK() [OK]

Common Mistakes:

• Forgetting OVER() clause
• Trying to put ORDER BY inside SELECT without OVER()
• Assuming RANK() takes parameters

5. You have a sales table:
Salesperson | SalesAmount
John | 500
Jane | 700
Joe | 700
Jill | 600
You want to assign ranks so that tied salespeople share the same rank and the next rank number is not skipped. Which query correctly achieves this?

hard

A. SELECT Salesperson, SalesAmount, RANK() OVER (ORDER BY SalesAmount DESC) AS Rank FROM sales;

B. SELECT Salesperson, SalesAmount, RANK() OVER (PARTITION BY SalesAmount ORDER BY SalesAmount DESC) AS Rank FROM sales;

C. SELECT Salesperson, SalesAmount, ROW_NUMBER() OVER (ORDER BY SalesAmount DESC) AS Rank FROM sales;

D. SELECT Salesperson, SalesAmount, DENSE_RANK() OVER (ORDER BY SalesAmount DESC) AS Rank FROM sales;

Solution

1. Step 1: Understand ranking requirements

   Tied salespeople should share the same rank, and the next rank number should not skip any numbers.

2. Step 2: Choose correct function

   DENSE_RANK() assigns same rank to ties and does not skip ranks. RANK() skips ranks after ties. ROW_NUMBER() never assigns same rank to ties. SELECT Salesperson, SalesAmount, RANK() OVER (PARTITION BY SalesAmount ORDER BY SalesAmount DESC) AS Rank FROM sales; misuses PARTITION BY.

3. Final Answer:

   SELECT Salesperson, SalesAmount, DENSE_RANK() OVER (ORDER BY SalesAmount DESC) AS Rank FROM sales; -> Option D

4. Quick Check:

   DENSE_RANK() for no skipped ranks after ties [OK]

Hint: Use DENSE_RANK() to avoid skipping ranks after ties [OK]

Common Mistakes:

• Using RANK() when no skipping is wanted
• Using ROW_NUMBER() which never ties
• Incorrect use of PARTITION BY in ranking