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