CTE materialization behavior in PostgreSQL - Mini Project: Build & Apply

Understanding CTE Materialization Behavior in PostgreSQL

📖 Scenario: You are working with a PostgreSQL database for a small online bookstore. You want to analyze sales data efficiently using Common Table Expressions (CTEs). Understanding how CTEs are materialized can help you write better queries that perform well.

🎯 Goal: Build a PostgreSQL query using a CTE to calculate total sales per author, then filter authors with sales above a certain threshold. Learn how CTE materialization affects query performance.

📋 What You'll Learn

Create a CTE named author_sales that sums sales per author from the sales table joined with books.

Define a threshold variable min_sales with value 1000.

Write a main query that selects authors from author_sales with total sales greater than min_sales.

Use PostgreSQL syntax and demonstrate CTE materialization behavior.

💡 Why This Matters

🌍 Real World

CTEs are often used in real-world databases to organize complex queries and improve readability. Understanding materialization helps optimize performance.

💼 Career

Database developers and analysts frequently write queries with CTEs. Knowing how PostgreSQL handles CTEs can help in tuning queries for faster results.

1

Create the author_sales CTE

Write a CTE named author_sales that selects author_id and the sum of amount as total_sales from the sales table joined with books on book_id. Group the results by author_id.

PostgreSQL

-- Write the CTE named author_sales here
WITH author_sales AS (
    -- Your code here
)

Need a hint?

Use WITH author_sales AS (SELECT ...) and join sales with books on book_id. Group by author_id and sum amount.

2

Define the min_sales threshold

Add a variable min_sales and set it to 1000 using a SELECT statement with WITH clause. This will be used as a filter threshold in the next step.

PostgreSQL

WITH author_sales AS (
    SELECT books.author_id, SUM(sales.amount) AS total_sales
    FROM sales
    JOIN books ON sales.book_id = books.book_id
    GROUP BY books.author_id
),
-- Define min_sales threshold here
min_sales AS (
    -- Your code here
)

Need a hint?

Use a CTE named min_sales that selects the number 1000 as value.

3

Write the main query filtering authors by min_sales

Write a main query that selects author_id and total_sales from author_sales where total_sales is greater than the value from min_sales. Use a join or cross join to access min_sales.value.

PostgreSQL

WITH author_sales AS (
    SELECT books.author_id, SUM(sales.amount) AS total_sales
    FROM sales
    JOIN books ON sales.book_id = books.book_id
    GROUP BY books.author_id
),
min_sales AS (
    SELECT 1000 AS value
)
-- Write the main query filtering authors here
SELECT author_id, total_sales
FROM author_sales
JOIN min_sales ON true
WHERE total_sales > min_sales.value

Need a hint?

Join author_sales with min_sales using ON true and filter with WHERE total_sales > min_sales.value.

4

Add MATERIALIZED keyword to the author_sales CTE

Modify the author_sales CTE to include the MATERIALIZED keyword explicitly to control materialization behavior in PostgreSQL.

PostgreSQL

-- Add MATERIALIZED keyword to author_sales CTE
WITH author_sales MATERIALIZED AS (
    SELECT books.author_id, SUM(sales.amount) AS total_sales
    FROM sales
    JOIN books ON sales.book_id = books.book_id
    GROUP BY books.author_id
),
min_sales AS (
    SELECT 1000 AS value
)
SELECT author_id, total_sales
FROM author_sales
JOIN min_sales ON true
WHERE total_sales > min_sales.value

Need a hint?

Insert the keyword MATERIALIZED right after author_sales in the CTE definition.