ROW_NUMBER, RANK, DENSE_RANK in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Difference between ROW_NUMBER and RANK

Consider a table sales with columns id, employee, and amount. The data is:

id | employee | amount
---+----------+-------
1  | Alice    | 100
2  | Bob      | 200
3  | Alice    | 200
4  | Bob      | 200
5  | Carol    | 300

What is the output of this query?

SELECT employee, amount, ROW_NUMBER() OVER (PARTITION BY amount ORDER BY employee) AS rn
FROM sales
ORDER BY amount, employee;

PostgreSQL

SELECT employee, amount, ROW_NUMBER() OVER (PARTITION BY amount ORDER BY employee) AS rn
FROM sales
ORDER BY amount, employee;

A [{"employee": "Alice", "amount": 100, "rn": 1}, {"employee": "Bob", "amount": 200, "rn": 1}, {"employee": "Alice", "amount": 200, "rn": 2}, {"employee": "Bob", "amount": 200, "rn": 3}, {"employee": "Carol", "amount": 300, "rn": 1}]

B [{"employee": "Alice", "amount": 100, "rn": 1}, {"employee": "Bob", "amount": 200, "rn": 1}, {"employee": "Bob", "amount": 200, "rn": 2}, {"employee": "Alice", "amount": 200, "rn": 3}, {"employee": "Carol", "amount": 300, "rn": 1}]

C [{"employee": "Alice", "amount": 100, "rn": 1}, {"employee": "Alice", "amount": 200, "rn": 1}, {"employee": "Bob", "amount": 200, "rn": 2}, {"employee": "Bob", "amount": 200, "rn": 3}, {"employee": "Carol", "amount": 300, "rn": 2}]

D [{"employee": "Alice", "amount": 100, "rn": 1}, {"employee": "Alice", "amount": 200, "rn": 1}, {"employee": "Bob", "amount": 200, "rn": 2}, {"employee": "Bob", "amount": 200, "rn": 3}, {"employee": "Carol", "amount": 300, "rn": 1}]

💡 Hint

ROW_NUMBER assigns a unique number to each row within the partition, ordered by the specified column.

ROW_NUMBER() restarts numbering for each amount group and orders by employee. For amount=200, Alice is first (rn=1), Bob is second (rn=2), and the second Bob is third (rn=3) because there are two rows with Bob and amount=200.

❓ query_result

intermediate

Understanding RANK with ties

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

SELECT employee, amount, RANK() OVER (ORDER BY amount DESC) AS rnk
FROM sales
ORDER BY amount DESC, employee;

PostgreSQL

SELECT employee, amount, RANK() OVER (ORDER BY amount DESC) AS rnk
FROM sales
ORDER BY amount DESC, employee;

A [{"employee": "Carol", "amount": 300, "rnk": 1}, {"employee": "Alice", "amount": 200, "rnk": 2}, {"employee": "Bob", "amount": 200, "rnk": 3}, {"employee": "Bob", "amount": 200, "rnk": 4}, {"employee": "Alice", "amount": 100, "rnk": 5}]

B [{"employee": "Carol", "amount": 300, "rnk": 1}, {"employee": "Alice", "amount": 200, "rnk": 2}, {"employee": "Bob", "amount": 200, "rnk": 2}, {"employee": "Bob", "amount": 200, "rnk": 2}, {"employee": "Alice", "amount": 100, "rnk": 5}]

C [{"employee": "Carol", "amount": 300, "rnk": 1}, {"employee": "Alice", "amount": 200, "rnk": 2}, {"employee": "Bob", "amount": 200, "rnk": 2}, {"employee": "Bob", "amount": 200, "rnk": 3}, {"employee": "Alice", "amount": 100, "rnk": 4}]

D [{"employee": "Carol", "amount": 300, "rnk": 1}, {"employee": "Alice", "amount": 200, "rnk": 2}, {"employee": "Bob", "amount": 200, "rnk": 2}, {"employee": "Bob", "amount": 200, "rnk": 2}, {"employee": "Alice", "amount": 100, "rnk": 4}]

💡 Hint

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

Carol has the highest amount 300, so rank 1. Alice and Bob with amount 200 tie for rank 2. Because there are 3 rows with amount 200, the next rank after 2 is 5 (2 + 3). Alice with amount 100 gets rank 5.

❓ query_result

advanced

DENSE_RANK behavior with duplicates

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

SELECT employee, amount, DENSE_RANK() OVER (ORDER BY amount DESC) AS drnk
FROM sales
ORDER BY amount DESC, employee;

PostgreSQL

SELECT employee, amount, DENSE_RANK() OVER (ORDER BY amount DESC) AS drnk
FROM sales
ORDER BY amount DESC, employee;

A [{"employee": "Carol", "amount": 300, "drnk": 1}, {"employee": "Alice", "amount": 200, "drnk": 2}, {"employee": "Bob", "amount": 200, "drnk": 2}, {"employee": "Bob", "amount": 200, "drnk": 2}, {"employee": "Alice", "amount": 100, "drnk": 3}]

B [{"employee": "Carol", "amount": 300, "drnk": 1}, {"employee": "Alice", "amount": 200, "drnk": 2}, {"employee": "Bob", "amount": 200, "drnk": 3}, {"employee": "Bob", "amount": 200, "drnk": 4}, {"employee": "Alice", "amount": 100, "drnk": 5}]

C [{"employee": "Carol", "amount": 300, "drnk": 1}, {"employee": "Alice", "amount": 200, "drnk": 2}, {"employee": "Bob", "amount": 200, "drnk": 3}, {"employee": "Bob", "amount": 200, "drnk": 3}, {"employee": "Alice", "amount": 100, "drnk": 4}]

D [{"employee": "Carol", "amount": 300, "drnk": 1}, {"employee": "Alice", "amount": 200, "drnk": 1}, {"employee": "Bob", "amount": 200, "drnk": 2}, {"employee": "Bob", "amount": 200, "drnk": 2}, {"employee": "Alice", "amount": 100, "drnk": 3}]

💡 Hint

DENSE_RANK() assigns ranks without gaps after ties.

Carol has the highest amount 300, rank 1. Alice and Bob with amount 200 tie for rank 2. Unlike RANK(), DENSE_RANK() does not skip ranks, so the next rank after 2 is 3 for amount 100.

🧠 Conceptual

advanced

Choosing the right ranking function

You want to assign a rank to sales amounts where ties get the same rank and the next rank number should not skip any numbers. Which function should you use?

A ROW_NUMBER()

B RANK()

C DENSE_RANK()

D COUNT()

💡 Hint

Think about which function assigns ranks without gaps after ties.

DENSE_RANK() assigns the same rank to ties and does not skip rank numbers after ties, unlike RANK(). ROW_NUMBER() assigns unique numbers even for ties.

📝 Syntax

expert

Identify the syntax error in window function usage

Which option contains a syntax error in this window function query?

SELECT employee, amount, RANK() OVER PARTITION BY amount ORDER BY employee) AS rnk FROM sales;

A SELECT employee, amount, RANK() OVER PARTITION BY amount ORDER BY employee AS rnk FROM sales;

B SELECT employee, amount, RANK() OVER (PARTITION BY amount ORDER BY employee) rnk FROM sales;

C SELECT employee, amount, RANK() OVER (PARTITION BY amount ORDER BY employee) AS rnk FROM sales;

D SELECT employee, amount, RANK() OVER (PARTITION BY amount ORDER BY employee) AS rnk FROM sales WHERE amount > 100;

💡 Hint

Check the parentheses and placement of the OVER clause.

Option A is missing parentheses around the OVER clause, causing a syntax error. The correct syntax requires parentheses enclosing PARTITION BY and ORDER BY inside OVER().

Practice

1. What does the ROW_NUMBER() function do in PostgreSQL?

easy

A. Counts the total number of rows in the table.

B. Assigns the same rank to rows with identical values, skipping ranks after ties.

C. Assigns ranks to rows without skipping numbers after ties.

D. Assigns a unique sequential number to each row in the result set.

Solution

1. Step 1: Understand ROW_NUMBER() purpose

   ROW_NUMBER() gives each row a unique sequential number, starting at 1.

2. Step 2: Compare with RANK() and DENSE_RANK()

   Unlike RANK() or DENSE_RANK(), ROW_NUMBER() never assigns the same number to multiple rows.

3. Final Answer:

   Assigns a unique sequential number to each row in the result set. -> Option D

4. Quick Check:

   ROW_NUMBER() = unique row numbers [OK]

Hint: ROW_NUMBER always gives unique numbers, no ties allowed [OK]

Common Mistakes:

• Confusing ROW_NUMBER with RANK or DENSE_RANK
• Thinking ROW_NUMBER skips numbers after ties
• Assuming ROW_NUMBER counts total rows

2. Which of the following is the correct syntax to assign a rank using RANK() over a column named score ordered descending?

easy

A. SELECT RANK(score) OVER (ORDER BY DESC) FROM table_name;

B. SELECT RANK() ORDER BY score DESC FROM table_name;

C. SELECT RANK() OVER (ORDER BY score DESC) FROM table_name;

D. SELECT RANK() OVER (PARTITION BY score DESC) FROM table_name;

Solution

1. Step 1: Recall correct RANK() syntax

   RANK() is used with OVER() clause specifying ORDER BY column and direction.

2. Step 2: Check each option

   SELECT RANK() OVER (ORDER BY score DESC) FROM table_name; correctly uses RANK() OVER (ORDER BY score DESC). Others misuse syntax or clauses.

3. Final Answer:

   SELECT RANK() OVER (ORDER BY score DESC) FROM table_name; -> Option C

4. Quick Check:

   RANK() OVER (ORDER BY column DESC) syntax [OK]

Hint: RANK() needs OVER with ORDER BY column [OK]

Common Mistakes:

• Putting column inside RANK() function
• Using PARTITION BY incorrectly
• Omitting OVER() clause

3. Given the table players with columns name and score, what will be the output of this query?

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

Data:
name | score -----|------ Alice | 100 Bob | 90 Carol | 90 Dave | 80

medium

A. [{"name": "Alice", "score": 100, "rank": 1}, {"name": "Bob", "score": 90, "rank": 1}, {"name": "Carol", "score": 90, "rank": 1}, {"name": "Dave", "score": 80, "rank": 2}]

B. [{"name": "Alice", "score": 100, "rank": 1}, {"name": "Bob", "score": 90, "rank": 2}, {"name": "Carol", "score": 90, "rank": 2}, {"name": "Dave", "score": 80, "rank": 4}]

C. [{"name": "Alice", "score": 100, "rank": 1}, {"name": "Bob", "score": 90, "rank": 2}, {"name": "Carol", "score": 90, "rank": 2}, {"name": "Dave", "score": 80, "rank": 3}]

D. [{"name": "Alice", "score": 100, "rank": 1}, {"name": "Bob", "score": 90, "rank": 2}, {"name": "Carol", "score": 90, "rank": 3}, {"name": "Dave", "score": 80, "rank": 4}]

Solution

1. Step 1: Understand RANK() behavior with ties

   RANK() assigns the same rank to tied scores and skips ranks after ties.

2. Step 2: Apply ranks to scores

   Alice has highest score 100 -> rank 1. Bob and Carol tie at 90 -> both rank 2. Next rank after tie skips to 4 for Dave.

3. Final Answer:

   [{"name": "Alice", "score": 100, "rank": 1}, {"name": "Bob", "score": 90, "rank": 2}, {"name": "Carol", "score": 90, "rank": 2}, {"name": "Dave", "score": 80, "rank": 4}] -> Option B

4. Quick Check:

   RANK() skips ranks after ties [OK]

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

Common Mistakes:

• Assuming ranks after ties are consecutive
• Confusing RANK() with DENSE_RANK()
• Misordering rows by score

4. Identify the error in this query:

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

medium

A. DENSE_RANK() should not have a column inside the parentheses.

B. DENSE_RANK() cannot be used without PARTITION BY clause.

C. ORDER BY clause is missing in the OVER() clause.

D. The query is correct and will run without errors.

Solution

1. Step 1: Check DENSE_RANK() syntax

   DENSE_RANK() does not take any arguments inside parentheses; it is called as DENSE_RANK() OVER (...).

2. Step 2: Identify the error in the query

   The query incorrectly uses DENSE_RANK(score), which causes a syntax error.

3. Final Answer:

   DENSE_RANK() should not have a column inside the parentheses. -> Option A

4. Quick Check:

   DENSE_RANK() has empty parentheses [OK]

Hint: Window functions have empty parentheses, column goes in ORDER BY [OK]

Common Mistakes:

• Putting column inside DENSE_RANK()
• Thinking PARTITION BY is mandatory
• Omitting OVER() clause

5. You want to assign ranks to employees based on their sales, but you want no gaps in ranking numbers even if some employees have the same sales. Which function should you use and why?

Options:
A) ROW_NUMBER() because it gives unique numbers.
B) RANK() because it skips ranks after ties.
C) DENSE_RANK() because it assigns ranks without gaps after ties.
D) COUNT() because it counts rows.

hard

A. DENSE_RANK() because it assigns ranks without gaps after ties.

B. RANK() because it skips ranks after ties.

C. ROW_NUMBER() because it gives unique numbers.

D. COUNT() because it counts rows.

Solution

1. Step 1: Understand ranking requirements

   The requirement is to assign ranks with no gaps even if employees tie in sales.

2. Step 2: Match functions to behavior

   ROW_NUMBER() gives unique numbers, ignoring ties. RANK() skips ranks after ties causing gaps. DENSE_RANK() assigns ranks without gaps after ties.

3. Step 3: Choose correct function

   DENSE_RANK() fits the requirement perfectly.

4. Final Answer:

   DENSE_RANK() because it assigns ranks without gaps after ties. -> Option A

5. Quick Check:

   DENSE_RANK() = no gaps in ranks [OK]

Hint: Use DENSE_RANK() to avoid gaps in ranking numbers [OK]

Common Mistakes:

• Choosing RANK() and expecting no gaps
• Using ROW_NUMBER() which ignores ties
• Confusing COUNT() with ranking functions