Module 19 — Common Table Expressions (CTEs)

1. Basic CTE

What it is: A Common Table Expression (CTE) is a named temporary result set defined with the WITH keyword. It exists only for the duration of the query and can be referenced by name in the main SELECT, INSERT, UPDATE, or DELETE.

Why we use it: CTEs make complex queries readable by breaking them into named, sequential steps. Instead of nested subqueries (hard to read inside-out), you write top-to-bottom like a story.

When we use it: When a subquery is complex, when the same subquery is used multiple times, or when you want to improve readability of multi-step queries.

-- Find products priced above their category average
WITH category_avg AS (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
)
SELECT p.name, p.price, ca.avg_price
FROM products p
JOIN category_avg ca ON p.category = ca.category
WHERE p.price > ca.avg_price;

2. Multiple CTEs

What it is: You can define multiple CTEs in a single WITH clause, separated by commas. Each CTE can reference previous CTEs, building up complex logic step by step.

Why we use it: Complex queries often need multiple intermediate results. Multiple CTEs let you compute each step independently, making the logic clear and maintainable.

When we use it: When building multi-step data pipelines, when different parts of the query need different filtering or aggregation, or when the query logic has distinct phases.

WITH
active_users AS (
    SELECT id, username FROM users WHERE is_active = true
),
recent_orders AS (
    SELECT user_id, SUM(total) AS total_spent
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT au.

3. CTE vs Subquery

What it is: CTEs and subqueries solve similar problems but with different syntax and readability tradeoffs. CTEs are named and sequential; subqueries are inline and can be nested.

Why we use it: Choosing between them affects code readability and maintainability. CTEs are preferred for complex queries; subqueries are fine for simple one-off checks.

When we use it: Use CTEs when the logic is complex, reusable, or needs recursion. Use subqueries for simple filters or comparisons.

4. Recursive CTEs

What it is: A recursive CTE references itself, allowing you to traverse hierarchical data (trees, graphs, org charts). It has two parts: an anchor query (base case) and a recursive query that joins back to the CTE.

Why we use it: Hierarchical data is everywhere — category trees, org charts, threaded comments, bill of materials. Without recursive CTEs, you'd need multiple queries or application-level recursion.

When we use it: When traversing tree structures, finding all descendants/ancestors, building breadcrumb paths, or any data with parent-child relationships.

-- Find all descendants in a category tree
WITH RECURSIVE category_tree AS (
    -- Base case: root categories
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL
 
    UNION ALL
 
    -- Recursive case: children
    SELECT c.id, c.name, 

How Recursive CTEs Work

5. CTE for Data Generation

What it is: CTEs can generate data using functions like generate_series(), creating temporary datasets for testing, filling gaps in time series, or building reference tables.

Why we use it: Sometimes you need a series of dates, numbers, or other values that don't exist in your tables. A CTE with generate_series creates them on the fly.

When we use it: When building time series with gaps filled, generating test data, creating calendar views, or building reference tables for reporting.

-- Generate a series of dates
WITH dates AS (
    SELECT generate_series(
        '2024-01-01'::DATE,
        '2024-12-31'::DATE,
        '1 day'::INTERVAL
    )::DATE AS date
)
SELECT date, EXTRACT(DOW FROM date) AS day_of_week

Common Mistakes to Avoid

1. Forgetting RECURSIVE keyword — Required for recursive CTEs
2. Missing base case in recursion — Causes infinite loops
3. Using CTEs for simple queries — Overkill for one-line subqueries

Practice Exercise

1. Write a CTE to find courses priced above the average
2. Write a CTE with two parts: one for published courses, one for draft courses, then combine them
3. Create a simple category tree table and write a recursive CTE to list all categories with their depth
4. Write a CTE to find the top 3 most expensive courses per instructor