SQL - Multiple CTEs in One Query | Application Architect

Key Insights

Multiple CTEs in a single query let you break complex logic into named, readable steps—chain them with commas after a single WITH clause, and later CTEs can reference earlier ones.
Use sequential CTEs when each step builds on the previous result; use independent CTEs when you need to combine unrelated data sources in your final SELECT.
CTEs aren’t always materialized—most databases inline them like subqueries, so don’t assume they cache results. Check your execution plan and consider indexed temp tables for expensive operations referenced multiple times.

Introduction to CTEs

Common Table Expressions transform unreadable nested subqueries into named, logical building blocks. Instead of deciphering a query from the inside out, you read it top to bottom like prose.

A CTE creates a temporary named result set that exists only for the duration of a single statement. You define it with the WITH keyword, give it a name, and then reference that name in subsequent parts of your query.

WITH active_users AS (
    SELECT user_id, email, last_login
    FROM users
    WHERE status = 'active'
)
SELECT * FROM active_users WHERE last_login > '2024-01-01';

This is straightforward. But real-world queries rarely need just one intermediate result. You need to filter, aggregate, join, rank, and combine data from multiple angles. That’s where multiple CTEs become essential.

Syntax for Chaining Multiple CTEs

The syntax is simple: define all your CTEs after a single WITH keyword, separated by commas. Each CTE gets a name, optional column list, and a query.

WITH 
    cte_one AS (
        SELECT column_a, column_b
        FROM table_one
        WHERE condition_one
    ),
    cte_two AS (
        SELECT column_c, column_d
        FROM table_two
        WHERE condition_two
    ),
    cte_three AS (
        SELECT column_e, column_f
        FROM table_three
        WHERE condition_three
    )
SELECT 
    cte_one.column_a,
    cte_two.column_c,
    cte_three.column_e
FROM cte_one
JOIN cte_two ON cte_one.column_a = cte_two.column_c
JOIN cte_three ON cte_two.column_c = cte_three.column_e;

No nested parentheses. No repeated subqueries. Each piece has a name that documents its purpose.

The key rules:

Only one WITH keyword at the start
Commas separate each CTE definition
No comma after the last CTE before the main SELECT
Later CTEs can reference earlier ones (but not vice versa)

Sequential CTEs: Building on Previous Results

Sequential CTEs create a data pipeline where each step transforms the output of the previous step. This is where CTEs truly shine—you can trace the logic from raw data to final result.

Consider a sales analysis that needs to filter orders, aggregate by region, then rank the results:

WITH 
    valid_orders AS (
        -- Step 1: Filter to completed orders from this year
        SELECT 
            order_id,
            region_id,
            customer_id,
            total_amount,
            order_date
        FROM orders
        WHERE status = 'completed'
          AND order_date >= '2024-01-01'
          AND total_amount > 0
    ),
    regional_totals AS (
        -- Step 2: Aggregate by region
        SELECT 
            region_id,
            COUNT(*) AS order_count,
            SUM(total_amount) AS total_revenue,
            COUNT(DISTINCT customer_id) AS unique_customers
        FROM valid_orders
        GROUP BY region_id
    ),
    ranked_regions AS (
        -- Step 3: Rank regions by revenue
        SELECT 
            region_id,
            order_count,
            total_revenue,
            unique_customers,
            RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank,
            total_revenue / unique_customers AS revenue_per_customer
        FROM regional_totals
    )
SELECT 
    r.region_name,
    rr.order_count,
    rr.total_revenue,
    rr.unique_customers,
    rr.revenue_rank,
    ROUND(rr.revenue_per_customer, 2) AS avg_revenue_per_customer
FROM ranked_regions rr
JOIN regions r ON rr.region_id = r.region_id
ORDER BY rr.revenue_rank;

Each CTE has one job. valid_orders handles filtering. regional_totals handles aggregation. ranked_regions handles ranking and calculated fields. The final SELECT just joins in the region names and formats output.

Try writing this as nested subqueries. You’ll end up with something nobody wants to debug at 2 AM.

Independent CTEs: Parallel Data Sources

Not every multi-CTE query is a pipeline. Sometimes you need to gather unrelated data from different sources and combine them in the final result.

WITH 
    customer_metrics AS (
        SELECT 
            COUNT(*) AS total_customers,
            COUNT(CASE WHEN created_at >= CURRENT_DATE - INTERVAL '30 days' 
                       THEN 1 END) AS new_customers_30d,
            AVG(lifetime_value) AS avg_ltv
        FROM customers
        WHERE status = 'active'
    ),
    product_metrics AS (
        SELECT 
            COUNT(*) AS total_products,
            COUNT(CASE WHEN stock_quantity = 0 THEN 1 END) AS out_of_stock,
            AVG(price) AS avg_price
        FROM products
        WHERE is_active = true
    ),
    order_metrics AS (
        SELECT 
            COUNT(*) AS orders_30d,
            SUM(total_amount) AS revenue_30d,
            AVG(total_amount) AS avg_order_value
        FROM orders
        WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
          AND status = 'completed'
    )
SELECT 
    cm.total_customers,
    cm.new_customers_30d,
    ROUND(cm.avg_ltv, 2) AS avg_customer_ltv,
    pm.total_products,
    pm.out_of_stock AS products_out_of_stock,
    ROUND(pm.avg_price, 2) AS avg_product_price,
    om.orders_30d AS orders_last_30_days,
    ROUND(om.revenue_30d, 2) AS revenue_last_30_days,
    ROUND(om.avg_order_value, 2) AS avg_order_value
FROM customer_metrics cm
CROSS JOIN product_metrics pm
CROSS JOIN order_metrics om;

These three CTEs don’t reference each other at all. They query different tables with different filters. The CROSS JOIN works because each CTE returns exactly one row of aggregated metrics.

This pattern keeps each metric calculation isolated and testable. Need to debug the customer count? Run just that CTE.

Practical Use Case: Report Generation

Here’s a realistic monthly dashboard query that combines sequential and independent CTE patterns:

WITH 
    -- Date boundaries for consistency
    report_period AS (
        SELECT 
            DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') AS period_start,
            DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 day' AS period_end
    ),
    
    -- Revenue metrics
    revenue_data AS (
        SELECT 
            SUM(o.total_amount) AS total_revenue,
            COUNT(DISTINCT o.order_id) AS total_orders,
            COUNT(DISTINCT o.customer_id) AS ordering_customers
        FROM orders o
        CROSS JOIN report_period rp
        WHERE o.order_date BETWEEN rp.period_start AND rp.period_end
          AND o.status = 'completed'
    ),
    
    -- New user acquisition
    new_users AS (
        SELECT 
            COUNT(*) AS new_user_count,
            COUNT(CASE WHEN first_order_date IS NOT NULL THEN 1 END) AS converted_users
        FROM users u
        CROSS JOIN report_period rp
        WHERE u.created_at BETWEEN rp.period_start AND rp.period_end
    ),
    
    -- Churn calculation
    churned_users AS (
        SELECT COUNT(DISTINCT customer_id) AS churn_count
        FROM (
            SELECT 
                c.customer_id,
                MAX(o.order_date) AS last_order
            FROM customers c
            LEFT JOIN orders o ON c.customer_id = o.customer_id
            CROSS JOIN report_period rp
            WHERE c.created_at < rp.period_start
            GROUP BY c.customer_id
            HAVING MAX(o.order_date) < rp.period_start - INTERVAL '90 days'
                OR MAX(o.order_date) IS NULL
        ) inactive
    ),
    
    -- Previous period for comparison
    previous_revenue AS (
        SELECT SUM(total_amount) AS prev_total_revenue
        FROM orders o
        CROSS JOIN report_period rp
        WHERE o.order_date BETWEEN 
              rp.period_start - INTERVAL '1 month' 
              AND rp.period_start - INTERVAL '1 day'
          AND o.status = 'completed'
    )
SELECT 
    TO_CHAR(rp.period_start, 'Month YYYY') AS report_month,
    rd.total_revenue,
    rd.total_orders,
    rd.ordering_customers,
    ROUND(rd.total_revenue / NULLIF(rd.total_orders, 0), 2) AS avg_order_value,
    nu.new_user_count,
    nu.converted_users,
    ROUND(100.0 * nu.converted_users / NULLIF(nu.new_user_count, 0), 1) AS conversion_rate_pct,
    cu.churn_count,
    ROUND(100.0 * (rd.total_revenue - pr.prev_total_revenue) 
          / NULLIF(pr.prev_total_revenue, 0), 1) AS revenue_growth_pct
FROM report_period rp
CROSS JOIN revenue_data rd
CROSS JOIN new_users nu
CROSS JOIN churned_users cu
CROSS JOIN previous_revenue pr;

The report_period CTE ensures all calculations use identical date boundaries. Each metric lives in its own CTE. The final SELECT just assembles and formats.

Performance Considerations

Here’s the uncomfortable truth: CTEs are not automatically materialized in most databases. PostgreSQL, MySQL, and SQL Server typically inline CTEs like subqueries. This means if you reference a CTE multiple times, the database might execute that query multiple times.

-- This CTE might execute twice
WITH expensive_calculation AS (
    SELECT customer_id, SUM(amount) AS total
    FROM large_orders_table
    GROUP BY customer_id
)
SELECT * FROM expensive_calculation WHERE total > 1000
UNION ALL
SELECT * FROM expensive_calculation WHERE total <= 1000;

PostgreSQL 12+ added the MATERIALIZED hint:

WITH expensive_calculation AS MATERIALIZED (
    SELECT customer_id, SUM(amount) AS total
    FROM large_orders_table
    GROUP BY customer_id
)
SELECT ...

For databases without this feature, consider:

Temporary tables for expensive operations referenced multiple times
Checking execution plans with EXPLAIN ANALYZE
Ensuring proper indexes on columns used in CTE WHERE clauses

Compare these approaches when performance matters:

-- CTE approach
EXPLAIN ANALYZE
WITH filtered AS (
    SELECT * FROM orders WHERE order_date > '2024-01-01'
)
SELECT region_id, SUM(amount) FROM filtered GROUP BY region_id;

-- Subquery approach (often identical plan)
EXPLAIN ANALYZE
SELECT region_id, SUM(amount) 
FROM (SELECT * FROM orders WHERE order_date > '2024-01-01') filtered 
GROUP BY region_id;

If the plans are identical, use whichever is more readable. If they differ, profile both with real data volumes.

Common Pitfalls and Best Practices

Name CTEs descriptively. cte1, cte2, temp tell you nothing. valid_orders, regional_totals, churned_users document intent.

Avoid circular references. CTE a cannot reference CTE b if b references a. The dependency graph must be acyclic.

Don’t over-CTE. A simple query doesn’t need five CTEs. If your logic is genuinely simple, keep it simple:

-- Overkill
WITH active AS (SELECT * FROM users WHERE active = true)
SELECT * FROM active WHERE created_at > '2024-01-01';

-- Just write this
SELECT * FROM users WHERE active = true AND created_at > '2024-01-01';

Consider alternatives for recursion. Recursive CTEs exist but have their own syntax and performance characteristics. That’s a separate topic.

Test CTEs individually. The beauty of named CTEs is that you can run each one independently during development. Use this for debugging.

Multiple CTEs transform complex SQL from a puzzle into a readable story. Use them when your query has distinct logical steps, when you’re combining independent data sources, or when you need to reference the same filtered dataset multiple times. Skip them when a simple query will do. The goal is always clarity for the next person reading your code—including future you.