Window functions (ROW_NUMBER) in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of ROW_NUMBER() with PARTITION BY

Given the table Employees with columns Department and Salary, what is the output of the following query?

SELECT Department, Salary, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rn FROM Employees;

MySQL

CREATE TABLE Employees (Department VARCHAR(20), Salary INT);
INSERT INTO Employees VALUES
('Sales', 5000),
('Sales', 7000),
('HR', 4500),
('HR', 4500),
('IT', 6000);

A [{"Department": "Sales", "Salary": 7000, "rn": 2}, {"Department": "Sales", "Salary": 5000, "rn": 1}, {"Department": "HR", "Salary": 4500, "rn": 2}, {"Department": "HR", "Salary": 4500, "rn": 1}, {"Department": "IT", "Salary": 6000, "rn": 1}]

B [{"Department": "Sales", "Salary": 7000, "rn": 1}, {"Department": "Sales", "Salary": 5000, "rn": 1}, {"Department": "HR", "Salary": 4500, "rn": 1}, {"Department": "HR", "Salary": 4500, "rn": 1}, {"Department": "IT", "Salary": 6000, "rn": 1}]

C [{"Department": "Sales", "Salary": 7000, "rn": 1}, {"Department": "Sales", "Salary": 5000, "rn": 2}, {"Department": "HR", "Salary": 4500, "rn": 1}, {"Department": "HR", "Salary": 4500, "rn": 2}, {"Department": "IT", "Salary": 6000, "rn": 1}]

D [{"Department": "Sales", "Salary": 5000, "rn": 1}, {"Department": "Sales", "Salary": 7000, "rn": 2}, {"Department": "HR", "Salary": 4500, "rn": 1}, {"Department": "HR", "Salary": 4500, "rn": 2}, {"Department": "IT", "Salary": 6000, "rn": 1}]

💡 Hint

Remember that ROW_NUMBER() resets for each partition and orders rows by Salary descending.

ROW_NUMBER() assigns a unique sequential number to rows within each Department partition, ordered by Salary descending. So the highest salary in each department gets rn = 1, the next rn = 2, and so on.

🧠 Conceptual

intermediate

Understanding ROW_NUMBER() without PARTITION BY

What will be the result of the following query on the Orders table?

SELECT OrderID, CustomerID, ROW_NUMBER() OVER (ORDER BY OrderDate) AS rn FROM Orders;

Assuming Orders has multiple rows with different OrderDate values.

A Each row gets a unique sequential number based on ascending OrderDate across the entire table.

B ROW_NUMBER() restarts numbering for each CustomerID.

C All rows get the same row number because no PARTITION BY is used.

D The query will cause a syntax error because PARTITION BY is missing.

💡 Hint

Think about how ROW_NUMBER() works when PARTITION BY is omitted.

Without PARTITION BY, ROW_NUMBER() numbers all rows in the entire result set ordered by OrderDate. It does not restart numbering for any group.

📝 Syntax

advanced

Identify the syntax error in ROW_NUMBER() usage

Which option contains a syntax error in using ROW_NUMBER() in MySQL?

MySQL

SELECT EmployeeID, ROW_NUMBER() OVER (PARTITION EmployeeID ORDER BY Salary) AS rn FROM Employees;

A SELECT EmployeeID, ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY Salary) AS rn FROM Employees;

B SELECT EmployeeID, ROW_NUMBER() OVER (ORDER BY Salary) AS rn FROM Employees;

C SELECT EmployeeID, ROW_NUMBER() OVER () AS rn FROM Employees;

D SELECT EmployeeID, ROW_NUMBER() OVER (PARTITION EmployeeID ORDER BY Salary) AS rn FROM Employees;

💡 Hint

Check the syntax of PARTITION BY clause in window functions.

The correct syntax requires PARTITION BY keyword. Option D misses the BY keyword causing a syntax error.

❓ optimization

advanced

Optimizing ROW_NUMBER() query for large datasets

You have a large Sales table and want to assign row numbers partitioned by Region ordered by SaleDate. Which approach is best for performance?

A Use ROW_NUMBER() OVER (PARTITION BY Region ORDER BY SaleDate) without any indexes.

B Create an index on (Region, SaleDate) and then use ROW_NUMBER() OVER (PARTITION BY Region ORDER BY SaleDate).

C Use a subquery with correlated subselect counting rows per Region and SaleDate instead of ROW_NUMBER().

D Use ROW_NUMBER() OVER (ORDER BY SaleDate) without partitioning.

💡 Hint

Indexes can speed up sorting and partitioning in window functions.

Creating an index on the partition and order columns helps the database efficiently compute ROW_NUMBER() by reducing sorting cost.

🔧 Debug

expert

Debugging unexpected ROW_NUMBER() results with ties

You run this query:

SELECT ProductID, Price, ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Price) AS rn FROM Products;

But you notice that products with the same Price in the same Category get different row numbers. Which is the cause?

A ROW_NUMBER() always assigns unique numbers even if values are tied, so ties get different row numbers.

B The ORDER BY clause is missing a secondary column to break ties, causing random order.

C You should use RANK() instead of ROW_NUMBER() to assign the same number to ties.

D ROW_NUMBER() cannot be used with PARTITION BY and ORDER BY together.

💡 Hint

Think about how ROW_NUMBER() handles ties compared to RANK().

ROW_NUMBER() always gives unique sequential numbers, so even tied rows get different numbers. To assign the same number to ties, use RANK() or DENSE_RANK().

Practice

1. What does the ROW_NUMBER() function do in MySQL window functions?

easy

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

B. Calculates the sum of values in a column.

C. Filters rows based on a condition.

D. Joins two tables based on a key.

Solution

1. Step 1: Understand the purpose of ROW_NUMBER()

   ROW_NUMBER() assigns a unique number to each row in the order specified by ORDER BY.

2. Step 2: Differentiate from other functions

   It does not sum, filter, or join; it only numbers rows uniquely.

3. Final Answer:

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

4. Quick Check:

   ROW_NUMBER() = unique row numbering [OK]

Hint: ROW_NUMBER() gives each row a unique number in order [OK]

Common Mistakes:

• Confusing ROW_NUMBER() with aggregate functions like SUM()
• Thinking ROW_NUMBER() filters rows
• Assuming ROW_NUMBER() joins tables

2. Which of the following is the correct syntax to assign row numbers partitioned by department and ordered by salary DESC?

easy

A. ROW_NUMBER(PARTITION BY department ORDER BY salary DESC)

B. ROW_NUMBER() PARTITION BY department ORDER BY salary DESC

C. ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC)

D. ROW_NUMBER() BY department ORDER BY salary DESC

Solution

1. Step 1: Recall correct ROW_NUMBER() syntax

   The correct syntax uses OVER() with PARTITION BY and ORDER BY inside parentheses.

2. Step 2: Identify correct option

   ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) correctly uses ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC).

3. Final Answer:

   ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) -> Option C

4. Quick Check:

   Correct syntax includes OVER() with PARTITION BY and ORDER BY [OK]

Hint: ROW_NUMBER() always needs OVER() with clauses inside [OK]

Common Mistakes:

• Omitting OVER() keyword
• Placing PARTITION BY outside parentheses
• Using incorrect keywords order

3. Given the table Employees with columns id, department, salary, what will be the output of this query?

SELECT id, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM Employees;

Assuming data:
id | department | salary
1 | Sales | 5000
2 | Sales | 7000
3 | HR | 6000
4 | HR | 5500

medium

A. [{id:2, department:'Sales', salary:7000, rn:2}, {id:1, department:'Sales', salary:5000, rn:1}, {id:3, department:'HR', salary:6000, rn:2}, {id:4, department:'HR', salary:5500, rn:1}]

B. [{id:1, department:'Sales', salary:5000, rn:2}, {id:2, department:'Sales', salary:7000, rn:1}, {id:3, department:'HR', salary:6000, rn:1}, {id:4, department:'HR', salary:5500, rn:2}]

C. [{id:1, department:'Sales', salary:5000, rn:1}, {id:2, department:'Sales', salary:7000, rn:2}, {id:4, department:'HR', salary:5500, rn:1}, {id:3, department:'HR', salary:6000, rn:2}]

D. [{id:1, department:'Sales', salary:5000, rn:1}, {id:2, department:'Sales', salary:7000, rn:1}, {id:3, department:'HR', salary:6000, rn:1}, {id:4, department:'HR', salary:5500, rn:1}]

Solution

1. Step 1: Partition rows by department

   Rows are grouped into Sales and HR departments separately.

2. Step 2: Order each partition by salary descending and assign row numbers

   Within Sales: id 2 (7000) gets rn=1, id 1 (5000) rn=2; Within HR: id 3 (6000) rn=1, id 4 (5500) rn=2.

3. Final Answer:

   [{id:1, department:'Sales', salary:5000, rn:2}, {id:2, department:'Sales', salary:7000, rn:1}, {id:3, department:'HR', salary:6000, rn:1}, {id:4, department:'HR', salary:5500, rn:2}] -> Option B

4. Quick Check:

   Partition by department + order by salary DESC = correct row numbers [OK]

Hint: Partition groups restart numbering; order controls row number sequence [OK]

Common Mistakes:

• Ignoring PARTITION BY and numbering globally
• Ordering ascending instead of descending
• Assigning same row number to multiple rows

4. Identify the error in this query:

SELECT id, ROW_NUMBER() PARTITION BY department ORDER BY salary FROM Employees;

medium

A. Missing OVER() keyword before PARTITION BY clause.

B. ORDER BY cannot be used inside ROW_NUMBER().

C. PARTITION BY should be after ORDER BY.

D. ROW_NUMBER() cannot be used with SELECT.

Solution

1. Step 1: Check ROW_NUMBER() syntax

   ROW_NUMBER() requires OVER() before PARTITION BY and ORDER BY clauses.

2. Step 2: Identify missing OVER()

   The query misses OVER(), causing syntax error.

3. Final Answer:

   Missing OVER() keyword before PARTITION BY clause. -> Option A

4. Quick Check:

   ROW_NUMBER() must have OVER() [OK]

Hint: Always use OVER() with ROW_NUMBER() [OK]

Common Mistakes:

• Omitting OVER() keyword
• Placing PARTITION BY outside OVER()
• Misordering clauses inside OVER()

5. You want to select the top 2 highest paid employees per department from the Employees table. Which query correctly uses ROW_NUMBER() to achieve this?

hard

A. SELECT id, department, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM Employees WHERE rn <= 2;

B. SELECT *, ROW_NUMBER() FROM Employees WHERE rn <= 2 PARTITION BY department ORDER BY salary DESC;

C. SELECT id, department, salary FROM Employees ORDER BY salary DESC LIMIT 2 PARTITION BY department;

D. SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM Employees) AS t WHERE rn <= 2;

Solution

1. Step 1: Use ROW_NUMBER() with PARTITION BY department and ORDER BY salary DESC

   This assigns row numbers restarting at 1 for each department, ordered by salary descending.

2. Step 2: Filter rows where row number is 2 or less

   Wrap the window function query as a subquery and filter with WHERE rn <= 2 to get top 2 per department.

3. Final Answer:

   SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM Employees) AS t WHERE rn <= 2; -> Option D

4. Quick Check:

   Use subquery with ROW_NUMBER() and filter rn <= 2 [OK]

Hint: Use subquery with ROW_NUMBER() and filter rn <= 2 [OK]

Common Mistakes:

• Filtering rn in outer query without subquery
• Using LIMIT with PARTITION BY incorrectly
• Omitting PARTITION BY causing global numbering