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

Using Multiple CTEs in One PostgreSQL Query

📖 Scenario: You work in a small bookstore's database team. The store wants to analyze its sales data to find the total sales per author and then identify authors with sales above a certain threshold.

🎯 Goal: Build a PostgreSQL query using multiple Common Table Expressions (CTEs) to first calculate total sales per book, then total sales per author, and finally select authors with sales above a threshold.

📋 What You'll Learn

Create a CTE named book_sales that sums sales per book.

Create a second CTE named author_sales that sums sales per author using book_sales.

Use a final SELECT statement to get authors with total sales greater than a threshold.

Use exact CTE names and column names as specified.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use multiple CTEs to organize complex queries for reporting and analysis.

💼 Career

Knowing how to write multiple CTEs is important for data analysts and database developers to write clear and efficient SQL queries.

1

Create the book_sales CTE

Write a CTE named book_sales that selects book_id and sums quantity as total_quantity from the sales table, grouping by book_id.

PostgreSQL

-- Write the book_sales CTE here
WITH book_sales AS (
    -- Your code here
)

Need a hint?

Use WITH book_sales AS (SELECT book_id, SUM(quantity) AS total_quantity FROM sales GROUP BY book_id).

2

Add the author_sales CTE

Add a second CTE named author_sales that joins book_sales with books on book_id, then sums total_quantity as author_total grouped by author.

PostgreSQL

WITH book_sales AS (
    SELECT book_id, SUM(quantity) AS total_quantity
    FROM sales
    GROUP BY book_id
),
-- Add the author_sales CTE here

Need a hint?

Join book_sales with books and group by b.author.

3

Add a threshold variable

Create a variable named sales_threshold and set it to 100 to filter authors with sales above this number.

PostgreSQL

WITH book_sales AS (
    SELECT book_id, SUM(quantity) AS total_quantity
    FROM sales
    GROUP BY book_id
),
author_sales AS (
    SELECT b.author, SUM(bs.total_quantity) AS author_total
    FROM book_sales bs
    JOIN books b ON bs.book_id = b.book_id
    GROUP BY b.author
)
-- Define sales_threshold variable here

Need a hint?

Use SELECT 100 AS sales_threshold to define the threshold.

4

Select authors with sales above the threshold

Write a final SELECT statement that selects author and author_total from author_sales where author_total is greater than 100.

PostgreSQL

WITH book_sales AS (
    SELECT book_id, SUM(quantity) AS total_quantity
    FROM sales
    GROUP BY book_id
),
author_sales AS (
    SELECT b.author, SUM(bs.total_quantity) AS author_total
    FROM book_sales bs
    JOIN books b ON bs.book_id = b.book_id
    GROUP BY b.author
)
-- Write the final SELECT statement here

Need a hint?

Filter authors with author_total > 100.