ROW_NUMBER function in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to assign a unique row number to each employee ordered by salary.

SQL

SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY [1]) AS row_num FROM employees;

Drag options to blanks, or click blank then click option'

Aname

Bemployee_id

Cdepartment

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column that does not exist in the table.

Forgetting to use ORDER BY inside the OVER clause.

The ROW_NUMBER() function assigns a unique number to each row based on the order of the salary column.

2fill in blank

medium

Complete the code to reset the row number for each department.

SQL

SELECT employee_id, department, ROW_NUMBER() OVER (PARTITION BY [1] ORDER BY salary) AS dept_rank FROM employees;

Drag options to blanks, or click blank then click option'

Aemployee_id

Bname

Cdepartment

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Using ORDER BY column inside PARTITION BY.

Omitting PARTITION BY when grouping is needed.

The PARTITION BY clause groups rows by department, so row numbers restart for each department.

3fill in blank

hard

Fix the error in the code to correctly assign row numbers partitioned by department and ordered by hire_date.

SQL

SELECT employee_id, department, ROW_NUMBER() OVER (PARTITION BY [1] ORDER BY [2]) AS rn FROM employees;

Drag options to blanks, or click blank then click option'

Ahire_date

Bemployee_id

Cdepartment

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping PARTITION BY and ORDER BY columns.

Using a non-existent column in either clause.

The PARTITION BY should be department to reset numbering per department, and ORDER BY should be hire_date to order employees by their hire date.

4fill in blank

hard

Fill both blanks to assign row numbers partitioned by department and ordered by salary descending.

SQL

SELECT employee_id, department, salary, ROW_NUMBER() OVER (PARTITION BY [1] ORDER BY salary [2]) AS rank FROM employees;

Drag options to blanks, or click blank then click option'

Adepartment

Bemployee_id

CDESC

DASC

Attempts:

3 left

💡 Hint

Common Mistakes

Using ASC instead of DESC for descending order.

Partitioning by the wrong column.

Partitioning by department groups employees, and ordering by salary DESC ranks highest salary first.

5fill in blank

hard

Fill all three blanks to select employee name, assign row numbers partitioned by department, ordered by hire_date ascending.

SQL

SELECT [1], ROW_NUMBER() OVER (PARTITION BY [2] ORDER BY [3]) AS row_num FROM employees;

Drag options to blanks, or click blank then click option'

Aemployee_name

Bdepartment

Chire_date

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Using salary instead of hire_date for ordering.

Selecting wrong column for employee name.

Select employee_name, partition by department to group employees, and order by hire_date ascending to number employees by hire date.

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