ROW_NUMBER function in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this ROW_NUMBER query?

Consider the table Employees with columns Department and Salary. What will be the row_num values after running this query?

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

SQL

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

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

A [{'Department': 'Sales', 'Salary': 7000, 'row_num': 1}, {'Department': 'Sales', 'Salary': 5000, 'row_num': 2}, {'Department': 'HR', 'Salary': 4500, 'row_num': 1}, {'Department': 'HR', 'Salary': 4000, 'row_num': 2}, {'Department': 'IT', 'Salary': 6000, 'row_num': 1}]

B [{'Department': 'Sales', 'Salary': 5000, 'row_num': 1}, {'Department': 'Sales', 'Salary': 7000, 'row_num': 2}, {'Department': 'HR', 'Salary': 4000, 'row_num': 1}, {'Department': 'HR', 'Salary': 4500, 'row_num': 2}, {'Department': 'IT', 'Salary': 6000, 'row_num': 1}]

C [{'Department': 'Sales', 'Salary': 7000, 'row_num': 2}, {'Department': 'Sales', 'Salary': 5000, 'row_num': 1}, {'Department': 'HR', 'Salary': 4500, 'row_num': 2}, {'Department': 'HR', 'Salary': 4000, 'row_num': 1}, {'Department': 'IT', 'Salary': 6000, 'row_num': 1}]

D [{'Department': 'Sales', 'Salary': 7000, 'row_num': 1}, {'Department': 'Sales', 'Salary': 5000, 'row_num': 1}, {'Department': 'HR', 'Salary': 4500, 'row_num': 1}, {'Department': 'HR', 'Salary': 4000, 'row_num': 1}, {'Department': 'IT', 'Salary': 6000, 'row_num': 1}]

💡 Hint

ROW_NUMBER() assigns unique numbers starting at 1 within each partition ordered by Salary descending.

The ROW_NUMBER() function restarts numbering for each Department (partition). It orders salaries descending, so highest salary gets row_num 1, next gets 2, and so on.

🧠 Conceptual

intermediate

What does the PARTITION BY clause do in ROW_NUMBER()?

In the ROW_NUMBER() function, what is the role of the PARTITION BY clause?

A It filters rows before numbering them.

B It limits the number of rows returned by the query.

C It orders the entire table before numbering rows.

D It divides the rows into groups where row numbering restarts for each group.

💡 Hint

Think about how numbering restarts for each department in a company.

PARTITION BY splits the data into groups. ROW_NUMBER() then numbers rows starting at 1 within each group separately.

📝 Syntax

advanced

Which query correctly uses ROW_NUMBER() to rank employees by salary?

Choose the query that correctly assigns a row number ranking employees by salary descending without partitioning.

A SELECT Name, Salary, ROW_NUMBER() PARTITION BY ORDER BY Salary DESC AS rank FROM Employees;

B SELECT Name, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rank FROM Employees;

C SELECT Name, Salary, ROW_NUMBER() OVER (PARTITION Salary ORDER BY DESC Salary) AS rank FROM Employees;

D SELECT Name, Salary, ROW_NUMBER() OVER ORDER BY Salary DESC AS rank FROM Employees;

💡 Hint

ROW_NUMBER() requires OVER() with ORDER BY inside parentheses.

Option B uses correct syntax: ROW_NUMBER() OVER (ORDER BY Salary DESC). Others have syntax errors or misplaced clauses.

❓ optimization

advanced

How to optimize a query using ROW_NUMBER() to get top 3 salaries per department?

Given a large Employees table, which query efficiently returns only the top 3 salaries per department using ROW_NUMBER()?

A SELECT * FROM (SELECT Department, Salary, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rn FROM Employees) sub WHERE rn <= 3;

B SELECT Department, Salary FROM Employees WHERE ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) <= 3;

C SELECT Department, Salary FROM Employees ORDER BY Salary DESC LIMIT 3;

D SELECT Department, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn FROM Employees WHERE rn <= 3;

💡 Hint

You need to filter after assigning row numbers in a subquery.

Option A assigns row numbers per department and filters for rn <= 3 in an outer query. Others either misuse ROW_NUMBER() or don't partition correctly.

🔧 Debug

expert

Why does this query cause an error?

Given the query:

SELECT Name, ROW_NUMBER() OVER (PARTITION BY Department) FROM Employees;

Why does it cause an error?

A Because ROW_NUMBER() requires a GROUP BY clause in the main query.

B Because PARTITION BY cannot be used without ORDER BY.

C Because ORDER BY clause is missing inside the OVER() clause.

D Because the Employees table must have a unique ID column for ROW_NUMBER() to work.

💡 Hint

ROW_NUMBER() needs to know how to order rows within each partition.

ROW_NUMBER() requires an ORDER BY clause inside OVER() to define the order of numbering. Missing ORDER BY causes syntax error.