Multiple CTEs in one query in SQL - Mini Project: Build & Apply

Using Multiple CTEs in One SQL Query

📖 Scenario: You work for a small bookstore. You have a database with sales data and book details. You want to find the total sales for each book and then find which books sold more than 50 copies.

🎯 Goal: Build a SQL query using two Common Table Expressions (CTEs). The first CTE calculates total sales per book. The second CTE filters books with sales greater than 50. Finally, select the book title and total sales from the filtered results.

📋 What You'll Learn

Create a CTE named BookSales that sums sales per book_id from the sales table.

Create a second CTE named TopSellingBooks that selects books from BookSales with total sales greater than 50.

Join TopSellingBooks with the books table to get the book titles.

Select the book title and total sales in the final output.

💡 Why This Matters

🌍 Real World

Using multiple CTEs helps break complex queries into smaller parts, making them easier to read and maintain in real business databases.

💼 Career

Many data analyst and database developer jobs require writing queries with multiple CTEs to organize data processing steps clearly.

1

Create the BookSales CTE

Write a SQL query that starts with a CTE named BookSales. This CTE should select book_id and the sum of quantity as total_sales from the sales table, grouped by book_id.

SQL

WITH BookSales AS (
    -- Your code here
)

Need a hint?

Use WITH BookSales AS (SELECT book_id, SUM(quantity) AS total_sales FROM sales GROUP BY book_id) to create the first CTE.

2

Add the TopSellingBooks CTE

Add a second CTE named TopSellingBooks after BookSales. This CTE should select all columns from BookSales where total_sales is greater than 50.

SQL

WITH BookSales AS (
    SELECT book_id, SUM(quantity) AS total_sales
    FROM sales
    GROUP BY book_id
),
TopSellingBooks AS (
    -- Your code here
)

Need a hint?

Use TopSellingBooks AS (SELECT * FROM BookSales WHERE total_sales > 50) to filter books with sales over 50.

3

Join TopSellingBooks with books table

After the two CTEs, write the main query that joins TopSellingBooks with the books table on book_id. Select books.title and TopSellingBooks.total_sales.

SQL

WITH BookSales AS (
    SELECT book_id, SUM(quantity) AS total_sales
    FROM sales
    GROUP BY book_id
),
TopSellingBooks AS (
    SELECT * FROM BookSales WHERE total_sales > 50
)
SELECT
    -- Your code here

Need a hint?

Use JOIN books ON TopSellingBooks.book_id = books.book_id to get book titles.

4

Complete the query with proper formatting

Ensure the full query uses commas correctly between CTEs and ends with a semicolon. The query should start with WITH BookSales AS, then TopSellingBooks AS, and finally the SELECT statement joining the tables.

SQL

WITH BookSales AS (
    SELECT book_id, SUM(quantity) AS total_sales
    FROM sales
    GROUP BY book_id
),
TopSellingBooks AS (
    SELECT * FROM BookSales WHERE total_sales > 50
)
SELECT
    books.title, TopSellingBooks.total_sales
FROM TopSellingBooks
JOIN books ON TopSellingBooks.book_id = books.book_id
-- Your code here

Need a hint?

End your query with a semicolon ; to complete it.