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.

Practice

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

easy

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

B. Filters rows based on a condition.

C. Calculates the sum of values in a column.

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

Solution

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

   The ROW_NUMBER() function assigns a unique sequential integer to rows in the result set, starting at 1.

2. Step 2: Compare with other options

   Counting rows, summing values, or filtering rows are done by other SQL functions like COUNT(), SUM(), or WHERE clause, not ROW_NUMBER().

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() gives each row a unique number starting at 1 [OK]

Common Mistakes:

• Confusing ROW_NUMBER() with COUNT()
• Thinking ROW_NUMBER() filters rows
• Assuming ROW_NUMBER() sums values

2. Which of the following is the correct syntax to assign row numbers ordered by salary in descending order?

easy

A. SELECT ROW_NUMBER() FROM employees ORDER BY salary DESC;

B. SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees;

C. SELECT ROW_NUMBER() ORDER BY salary DESC FROM employees;

D. SELECT ROW_NUMBER(salary DESC) FROM employees;

Solution

1. Step 1: Identify correct ROW_NUMBER() syntax

   The correct syntax uses ROW_NUMBER() OVER (ORDER BY column) to assign numbers ordered by a column.

2. Step 2: Check each option

   Only SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees; correctly uses OVER (ORDER BY salary DESC). The other options omit the OVER clause, pass arguments directly to ROW_NUMBER(), or misplace the ORDER BY clause.

3. Final Answer:

   SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees; -> Option B

4. Quick Check:

   ROW_NUMBER() needs OVER with ORDER BY [OK]

Hint: ROW_NUMBER() must use OVER() with ORDER BY inside [OK]

Common Mistakes:

• Omitting OVER() clause
• Placing ORDER BY outside OVER()
• Passing column directly to ROW_NUMBER()

3. Given the table sales with columns region and amount, what will this query return?

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

medium

A. The total count of sales per region.

B. A unique row number for all sales ignoring region, ordered by amount ascending.

C. A unique row number for each sale, restarting at 1 for each region, ordered by highest amount first.

D. The sum of amounts per region.

Solution

1. Step 1: Understand PARTITION BY effect

   Using PARTITION BY region restarts the row numbering for each region group separately.

2. Step 2: Understand ORDER BY inside ROW_NUMBER()

   Ordering by amount DESC means the highest amount in each region gets row number 1, next highest 2, and so on.

3. Final Answer:

   A unique row number for each sale, restarting at 1 for each region, ordered by highest amount first. -> Option C

4. Quick Check:

   ROW_NUMBER() with PARTITION BY = numbering per group [OK]

Hint: PARTITION BY restarts numbering per group [OK]

Common Mistakes:

• Ignoring PARTITION BY and numbering all rows globally
• Confusing ORDER BY direction
• Thinking ROW_NUMBER() counts rows

4. Identify the error in this query:

SELECT name, ROW_NUMBER() ORDER BY salary FROM employees;

medium

A. Missing OVER() clause after ROW_NUMBER().

B. ORDER BY cannot be used in SELECT clause.

C. ROW_NUMBER() cannot be used without GROUP BY.

D. salary column does not exist.

Solution

1. Step 1: Check ROW_NUMBER() syntax

   The ROW_NUMBER() function requires an OVER() clause specifying ordering or partitioning.

2. Step 2: Analyze the query

   The query uses ROW_NUMBER() without OVER(), which causes a syntax error. ORDER BY inside SELECT is invalid without OVER().

3. Final Answer:

   Missing OVER() clause after ROW_NUMBER(). -> Option A

4. Quick Check:

   ROW_NUMBER() must have OVER() [OK]

Hint: ROW_NUMBER() always needs OVER() clause [OK]

Common Mistakes:

• Forgetting OVER() after ROW_NUMBER()
• Placing ORDER BY outside OVER()
• Assuming ROW_NUMBER() works without ordering

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 * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) sub WHERE rn <= 2;

B. SELECT *, ROW_NUMBER() FROM employees WHERE salary IN (SELECT MAX(salary) FROM employees GROUP BY department);

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

D. SELECT department, salary FROM employees ORDER BY salary DESC LIMIT 2;

Solution

1. Step 1: Use ROW_NUMBER() with PARTITION BY department

   To get top 2 per department, assign row numbers restarting for each department ordered by salary descending.

2. Step 2: Filter rows with row number less than or equal to 2

   Wrap the query as a subquery and filter where rn <= 2 to get top 2 employees per department.

3. Final Answer:

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

4. Quick Check:

   ROW_NUMBER() + PARTITION + filter = top N per group [OK]

Hint: Use ROW_NUMBER() with PARTITION BY and filter rn <= N [OK]

Common Mistakes:

• Not using PARTITION BY to group by department
• Filtering on rn in the same SELECT without subquery
• Using LIMIT without partitioning