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