ROW_NUMBER function in SQL - Mini Project: Build & Apply

Using the ROW_NUMBER Function in SQL

📖 Scenario: You work at a bookstore that wants to list books with a unique rank number based on their sales. This helps the store see which books are the top sellers.

🎯 Goal: Create a SQL query that assigns a row number to each book ordered by the number of sales, starting from 1 for the highest-selling book.

📋 What You'll Learn

Create a table called books with columns id, title, and sales.

Insert exactly three rows into the books table with specified values.

Write a query that uses the ROW_NUMBER() function to assign a rank to each book ordered by sales in descending order.

Select the id, title, sales, and the row number as rank.

💡 Why This Matters

🌍 Real World

Ranking items by sales or popularity is common in business reports, leaderboards, and dashboards.

💼 Career

Understanding ROW_NUMBER() helps in writing advanced SQL queries for data analysis and reporting roles.

1

Create the books table

Write a SQL statement to create a table called books with three columns: id as an integer primary key, title as text, and sales as an integer.

SQL

-- Create the books table with columns id, title, and sales
-- Your code here

Need a hint?

Use CREATE TABLE books and define the columns with their types. Make id the primary key.

2

Insert book data

Insert exactly three rows into the books table with these values: (1, 'The Great Gatsby', 30), (2, '1984', 45), and (3, 'To Kill a Mockingbird', 25).

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  sales INTEGER
);
-- Insert three rows into books
-- Your code here

Need a hint?

Use INSERT INTO books (id, title, sales) VALUES followed by the three tuples.

3

Write the ROW_NUMBER query

Write a SQL query that selects id, title, sales, and a new column called rank which uses the ROW_NUMBER() function ordered by sales in descending order from the books table.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  sales INTEGER
);

INSERT INTO books (id, title, sales) VALUES
  (1, 'The Great Gatsby', 30),
  (2, '1984', 45),
  (3, 'To Kill a Mockingbird', 25);

-- Write a SELECT query using ROW_NUMBER() to rank books by sales
-- Your code here

Need a hint?

Use ROW_NUMBER() OVER (ORDER BY sales DESC) to assign ranks starting from the highest sales.

4

Complete the ranking query

Add an ORDER BY rank clause at the end of the query to show the books sorted by their rank in ascending order.

SQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  sales INTEGER
);

INSERT INTO books (id, title, sales) VALUES
  (1, 'The Great Gatsby', 30),
  (2, '1984', 45),
  (3, 'To Kill a Mockingbird', 25);

SELECT id, title, sales,
  ROW_NUMBER() OVER (ORDER BY sales DESC) AS rank
FROM books
-- Add ORDER BY rank here
-- Your code here

Need a hint?

Use ORDER BY rank to sort the results by the row number.