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

Need a 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

Need a 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

Need a 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

Need a hint?

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