Window functions (ROW_NUMBER) in MySQL - Mini Project: Build & Apply

Assigning Row Numbers with ROW_NUMBER() in MySQL

📖 Scenario: You work at a bookstore that keeps track of book sales. Each sale record includes the book title, the sale date, and the number of copies sold. You want to assign a unique row number to each sale ordered by the sale date to identify the sequence of sales.

🎯 Goal: Build a SQL query that uses the ROW_NUMBER() window function to assign a row number to each sale ordered by sale_date.

📋 What You'll Learn

Create a table called book_sales with columns id, book_title, sale_date, and copies_sold.

Insert exactly 5 rows into book_sales with specified values.

Write a query that selects all columns and adds a row_num column using ROW_NUMBER() ordered by sale_date ascending.

Ensure the final query includes the row_num column.

💡 Why This Matters

🌍 Real World

Assigning row numbers helps in ranking or ordering records in reports, such as sales sequences or event logs.

💼 Career

Understanding window functions like ROW_NUMBER() is essential for data analysts and database developers to write advanced queries for business insights.

1

Create the book_sales table and insert data

Create a table called book_sales with columns id (integer primary key), book_title (varchar 100), sale_date (date), and copies_sold (integer). Then insert these exact rows: (1, 'The Alchemist', '2023-01-10', 3), (2, '1984', '2023-01-12', 5), (3, 'The Alchemist', '2023-01-15', 2), (4, 'Brave New World', '2023-01-11', 4), (5, '1984', '2023-01-13', 1).

MySQL

-- Create the book_sales table and insert the 5 rows below
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows exactly as given.

2

Set up the base SELECT query

Write a SELECT query to retrieve all columns from book_sales. Use SELECT id, book_title, sale_date, copies_sold FROM book_sales as the base for the next step.

MySQL

CREATE TABLE book_sales (
  id INT PRIMARY KEY,
  book_title VARCHAR(100),
  sale_date DATE,
  copies_sold INT
);

INSERT INTO book_sales (id, book_title, sale_date, copies_sold) VALUES
(1, 'The Alchemist', '2023-01-10', 3),
(2, '1984', '2023-01-12', 5),
(3, 'The Alchemist', '2023-01-15', 2),
(4, 'Brave New World', '2023-01-11', 4),
(5, '1984', '2023-01-13', 1);

-- Write a SELECT query to get id, book_title, sale_date, copies_sold from book_sales
-- Your code here

Hint

Use SELECT with the exact column names and table name.

3

Add the ROW_NUMBER() window function

Modify the SELECT query to add a new column called row_num that uses ROW_NUMBER() OVER (ORDER BY sale_date ASC) to assign row numbers ordered by sale_date ascending.

MySQL

CREATE TABLE book_sales (
  id INT PRIMARY KEY,
  book_title VARCHAR(100),
  sale_date DATE,
  copies_sold INT
);

INSERT INTO book_sales (id, book_title, sale_date, copies_sold) VALUES
(1, 'The Alchemist', '2023-01-10', 3),
(2, '1984', '2023-01-12', 5),
(3, 'The Alchemist', '2023-01-15', 2),
(4, 'Brave New World', '2023-01-11', 4),
(5, '1984', '2023-01-13', 1);

-- Add a row_num column using ROW_NUMBER() ordered by sale_date ascending
-- Your code here

Hint

Use ROW_NUMBER() OVER (ORDER BY sale_date ASC) and alias it as row_num.

4

Complete the query with ordering by row_num

Add an ORDER BY row_num clause at the end of the SELECT query to display the results ordered by the assigned row numbers.

MySQL

CREATE TABLE book_sales (
  id INT PRIMARY KEY,
  book_title VARCHAR(100),
  sale_date DATE,
  copies_sold INT
);

INSERT INTO book_sales (id, book_title, sale_date, copies_sold) VALUES
(1, 'The Alchemist', '2023-01-10', 3),
(2, '1984', '2023-01-12', 5),
(3, 'The Alchemist', '2023-01-15', 2),
(4, 'Brave New World', '2023-01-11', 4),
(5, '1984', '2023-01-13', 1);

-- Add ORDER BY row_num at the end of the query
-- Your code here

Hint

Use ORDER BY row_num to sort the results by the row numbers.

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