Common Table Expressions - Learning Module

Loading content...

0/252

Multiple CTEs

Composing Complex Queries from Simple Parts

A single CTE is powerful. Multiple CTEs are transformative. When you combine several CTEs in one query, you unlock the ability to build data transformation pipelines—sequences of operations that progressively refine raw data into precisely the result you need.

This approach mirrors how data engineers think about ETL pipelines: extract base data, transform through multiple stages, load into the final form. With multiple CTEs, this entire pipeline can exist within a single SQL statement, executed atomically, optimized holistically, and maintained as a coherent unit.

What You Will Learn

By the end of this page, you will master the syntax for defining multiple CTEs, understand dependency ordering and reference rules, learn techniques for managing complex CTE chains, and develop patterns for building sophisticated analytical queries that would be nearly impossible with traditional subqueries.

Multiple CTE Syntax

The syntax for multiple CTEs extends naturally from single CTE syntax. CTEs are defined sequentially within a single WITH clause, separated by commas.

multiple_cte_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Multiple CTE Syntax Pattern
WITH 
    -- First CTE
    first_cte AS (
        SELECT column1, column2
        FROM table1
        WHERE condition1
    ),
    
    -- Second CTE (can reference first_cte)
    second_cte AS (
        SELECT column1, calculated_field
        FROM first_cte
        WHERE condition2
    ),
    
    -- Third CTE (can reference first_cte and second_cte)
    third_cte AS (
        SELECT 
            f.column1,
            s.calculated_field,
            additional_data
        FROM first_cte f
        INNER JOIN second_cte s ON f.column1 = s.column1
    )
    
    -- Note: No comma after the last CTE
 
-- Main query can reference any CTE
SELECT *
FROM third_cte
ORDER BY column1;

Common Syntax Errors

The most common errors with multiple CTEs: 1) Missing comma between CTEs, 2) Extra comma after the last CTE before the main query, 3) Using WITH before each CTE instead of just once at the start. Remember: one WITH, multiple CTEs separated by commas.

Multiple CTE Syntax Rules
Rule	Correct	Incorrect
WITH keyword	Once at the start	WITH before each CTE
CTE separator	Comma between CTEs	Semicolon or nothing
After last CTE	No comma	Comma before main query
Reference order	Later can reference earlier	Earlier referencing later
Main query	Immediately follows last CTE	Separated by semicolon

CTE Dependency Chains

When you have multiple CTEs, they form a dependency graph. Understanding this graph is essential for designing effective queries. CTEs can only reference CTEs defined before them in the WITH clause.

cte_dependency_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
-- PATTERN 1: Linear Chain (Pipeline)
-- Each CTE depends only on the immediately preceding CTE
-- A → B → C → D
 
WITH 
    raw_data AS (
        SELECT * FROM source_table
    ),
    cleaned_data AS (
        -- Depends on: raw_data
        SELECT * FROM raw_data WHERE valid = TRUE
    ),
    enriched_data AS (
        -- Depends on: cleaned_data
        SELECT cd.*, ref.label
        FROM cleaned_data cd
        JOIN reference ref ON cd.type_id = ref.id
    ),
    aggregated_data AS (
        -- Depends on: enriched_data
        SELECT label, COUNT(*), SUM(amount)
        FROM enriched_data
        GROUP BY label
    )
SELECT * FROM aggregated_data;
 
 
-- PATTERN 2: Diamond (Multiple paths converge)
--      A
--     / \
--    B   C
--     \ /
--      D
 
WITH 
    base_transactions AS (
        SELECT transaction_id, customer_id, amount, category
        FROM transactions
        WHERE transaction_date >= CURRENT_DATE - INTERVAL '30 days'
    ),
    customer_totals AS (
        -- Depends on: base_transactions
        SELECT customer_id, SUM(amount) as customer_total
        FROM base_transactions
        GROUP BY customer_id
    ),
    category_totals AS (
        -- Depends on: base_transactions (parallel branch)
        SELECT category, SUM(amount) as category_total
        FROM base_transactions
        GROUP BY category
    ),
    combined_analysis AS (
        -- Depends on: base_transactions, customer_totals, category_totals
        SELECT 
            bt.transaction_id,
            bt.amount,
            ct.customer_total,
            cat.category_total,
            bt.amount / ct.customer_total as pct_of_customer,
            bt.amount / cat.category_total as pct_of_category
        FROM base_transactions bt
        JOIN customer_totals ct USING (customer_id)
        JOIN category_totals cat USING (category)
    )
SELECT * FROM combined_analysis;
 
 
-- PATTERN 3: Fan-Out (One source, multiple consumers)
--        A
--      / | \
--     B  C  D 
 
WITH 
    all_orders AS (
        SELECT order_id, customer_id, order_date, amount, region
        FROM orders
        WHERE status = 'completed'
    ),
    orders_by_customer AS (
        SELECT customer_id, COUNT(*) as orders, SUM(amount) as revenue
        FROM all_orders
        GROUP BY customer_id
    ),
    orders_by_region AS (
        SELECT region, COUNT(*) as orders, SUM(amount) as revenue
        FROM all_orders
        GROUP BY region
    ),
    orders_by_month AS (
        SELECT DATE_TRUNC('month', order_date) as month, 
               COUNT(*) as orders, SUM(amount) as revenue
        FROM all_orders
        GROUP BY DATE_TRUNC('month', order_date)
    )
-- Main query might use any or all of these
SELECT 'by_customer' as metric, COUNT(*) as records FROM orders_by_customer
UNION ALL
SELECT 'by_region', COUNT(*) FROM orders_by_region
UNION ALL
SELECT 'by_month', COUNT(*) FROM orders_by_month;

Dependency Pattern Guidelines

•Linear chains — Best for step-by-step transformations; easy to follow and debug
•Diamond patterns — Useful when different aggregations need the same base data
•Fan-out patterns — Efficient when one expensive filter/join feeds multiple analyses
•Keep depth manageable — More than 5-7 CTEs often signals a query that should be broken into multiple statements or views
•Avoid unnecessary dependencies — If a CTE doesn't need another CTE, don't reference it

Reference Flexibility

CTEs can be referenced multiple times within the same query—from later CTEs, from the main query, or from subqueries within either. This flexibility enables powerful patterns.

cte_reference_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
-- REFERENCE PATTERN 1: Self-comparison
-- Compare aggregates from the same CTE
WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', sale_date) as month,
        SUM(amount) as revenue
    FROM sales
    WHERE sale_date >= CURRENT_DATE - INTERVAL '2 years'
    GROUP BY DATE_TRUNC('month', sale_date)
)
SELECT 
    current.month,
    current.revenue as current_revenue,
    prior.revenue as prior_month_revenue,
    yoy.revenue as same_month_last_year,
    current.revenue - prior.revenue as mom_change,
    current.revenue - yoy.revenue as yoy_change
FROM monthly_sales current
LEFT JOIN monthly_sales prior 
    ON current.month = prior.month + INTERVAL '1 month'
LEFT JOIN monthly_sales yoy 
    ON current.month = yoy.month + INTERVAL '1 year'
ORDER BY current.month;
 
 
-- REFERENCE PATTERN 2: Cross-reference between CTEs
WITH 
    products AS (
        SELECT product_id, product_name, category_id
        FROM product_catalog
        WHERE status = 'active'
    ),
    product_sales AS (
        SELECT product_id, SUM(quantity) as units_sold, SUM(revenue) as revenue
        FROM order_items
        WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
        GROUP BY product_id
    ),
    category_averages AS (
        -- References both previous CTEs
        SELECT 
            p.category_id,
            AVG(ps.units_sold) as avg_units,
            AVG(ps.revenue) as avg_revenue
        FROM products p
        LEFT JOIN product_sales ps USING (product_id)
        GROUP BY p.category_id
    )
-- Main query references all three
SELECT 
    p.product_name,
    ps.units_sold,
    ps.revenue,
    ca.avg_units as category_avg_units,
    ps.units_sold - ca.avg_units as units_vs_category_avg,
    CASE 
        WHEN ps.units_sold > ca.avg_units * 1.5 THEN 'Star Performer'
        WHEN ps.units_sold < ca.avg_units * 0.5 THEN 'Underperformer'
        ELSE 'Average'
    END as performance_tier
FROM products p
LEFT JOIN product_sales ps USING (product_id)
LEFT JOIN category_averages ca USING (category_id)
ORDER BY ps.revenue DESC NULLS LAST;
 
 
-- REFERENCE PATTERN 3: CTE in subquery
WITH active_customers AS (
    SELECT customer_id, customer_name, region
    FROM customers
    WHERE status = 'active'
    AND last_purchase_date >= CURRENT_DATE - INTERVAL '90 days'
)
SELECT 
    region,
    (SELECT COUNT(*) FROM active_customers ac 
     WHERE ac.region = regions.region) as active_count,
    (SELECT SUM(amount) FROM orders o 
     WHERE o.customer_id IN (
         SELECT customer_id FROM active_customers ac 
         WHERE ac.region = regions.region
     )) as region_revenue
FROM (SELECT DISTINCT region FROM active_customers) regions;

Multi-Reference Performance

When a CTE is referenced multiple times, the database may materialize it (compute once, store temporarily) rather than re-execute. This can dramatically improve performance when the CTE contains expensive operations like aggregations or complex joins. However, behavior varies by database—check your execution plan.

Building Data Pipelines

Multiple CTEs enable you to build complete data pipelines within a single query. Each CTE represents a stage in the pipeline: extraction, cleansing, transformation, enrichment, aggregation, and presentation.

complete_data_pipeline.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
-- COMPLETE DATA PIPELINE: Cohort Analysis
-- Business Question: How do customer cohorts perform over time?
 
WITH 
    -- STAGE 1: EXTRACTION
    -- Extract raw order data with customer information
    raw_orders AS (
        SELECT 
            o.order_id,
            o.customer_id,
            o.order_date,
            o.amount,
            c.registration_date,
            c.acquisition_source
        FROM orders o
        INNER JOIN customers c ON o.customer_id = c.customer_id
        WHERE o.status = 'completed'
        AND o.order_date >= '2023-01-01'
    ),
    
    -- STAGE 2: CLEANSING
    -- Remove invalid records, handle edge cases
    clean_orders AS (
        SELECT *
        FROM raw_orders
        WHERE amount > 0
        AND registration_date <= order_date  -- Registration before first order
        AND registration_date >= '2020-01-01'  -- Reasonable date range
    ),
    
    -- STAGE 3: ENRICHMENT
    -- Add derived fields needed for analysis
    enriched_orders AS (
        SELECT 
            *,
            DATE_TRUNC('month', registration_date) as cohort_month,
            DATE_TRUNC('month', order_date) as order_month,
            -- Calculate months since registration
            (EXTRACT(YEAR FROM order_date) - EXTRACT(YEAR FROM registration_date)) * 12 +
            (EXTRACT(MONTH FROM order_date) - EXTRACT(MONTH FROM registration_date)) 
                as months_since_registration
        FROM clean_orders
    ),
    
    -- STAGE 4: AGGREGATION (Level 1)
    -- Aggregate to customer-month level
    customer_month_metrics AS (
        SELECT 
            customer_id,
            cohort_month,
            order_month,
            months_since_registration,
            COUNT(DISTINCT order_id) as orders,
            SUM(amount) as revenue
        FROM enriched_orders
        GROUP BY 
            customer_id, 
            cohort_month, 
            order_month, 
            months_since_registration
    ),
    
    -- STAGE 5: AGGREGATION (Level 2)
    -- Aggregate to cohort-period level for cohort analysis
    cohort_analysis AS (
        SELECT 
            cohort_month,
            months_since_registration as period,
            COUNT(DISTINCT customer_id) as active_customers,
            SUM(orders) as total_orders,
            SUM(revenue) as total_revenue,
            AVG(revenue) as avg_revenue_per_customer
        FROM customer_month_metrics
        GROUP BY cohort_month, months_since_registration
    ),
    
    -- STAGE 6: ENRICHMENT (Level 2)
    -- Add cohort size for retention calculation
    cohort_sizes AS (
        SELECT 
            cohort_month,
            COUNT(DISTINCT customer_id) as cohort_size
        FROM clean_orders
        GROUP BY cohort_month
    ),
    
    -- STAGE 7: FINAL TRANSFORMATION
    -- Calculate retention rates and format for presentation
    cohort_retention AS (
        SELECT 
            ca.cohort_month,
            cs.cohort_size,
            ca.period,
            ca.active_customers,
            ca.total_revenue,
            ROUND(ca.active_customers::numeric / cs.cohort_size * 100, 1) 
                as retention_rate,
            ROUND(ca.total_revenue / ca.active_customers, 2) 
                as revenue_per_active
        FROM cohort_analysis ca
        INNER JOIN cohort_sizes cs USING (cohort_month)
    )
 
-- PRESENTATION LAYER
SELECT 
    TO_CHAR(cohort_month, 'YYYY-MM') as cohort,
    cohort_size as initial_customers,
    period as months_after_signup,
    active_customers,
    retention_rate || '%' as retention,
    total_revenue,
    revenue_per_active as arpc
FROM cohort_retention
WHERE period <= 12  -- First year only
ORDER BY cohort_month, period;

Pipeline Stage Purposes
Stage	Purpose	Typical Operations
Extraction	Gather raw data from source tables	SELECT, JOIN source tables
Cleansing	Remove invalid/incomplete records	WHERE clauses, NULL handling
Enrichment	Add calculated fields, lookups	CASE, date calculations, JOINs
Aggregation	Summarize to required granularity	GROUP BY, aggregate functions
Transformation	Reshape for final needs	Pivoting, calculations on aggregates
Presentation	Format for output/consumption	Formatting, final ordering

Managing CTE Complexity

As queries grow to include many CTEs, complexity management becomes crucial. Without discipline, multi-CTE queries can become as hard to maintain as the nested subqueries they replace.

Complexity Management Strategies

•Limit CTE count — If you exceed 7-10 CTEs, consider whether the query should be split or use views
•Consistent naming convention — Use a pattern like stage_description or description_granularity
•Group related CTEs visually — Add blank lines and comments between logical groups
•Document dependencies — Comment which CTEs each CTE depends on when not obvious
•Order by execution concept — Arrange CTEs in the order they logically execute, not alphabetically

complexity_management.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
-- WELL-ORGANIZED Multi-CTE Query
 
WITH 
    -- ═══════════════════════════════════════════════
    -- BASE DATA CTEs
    -- ═══════════════════════════════════════════════
    
    -- All active orders in reporting period
    -- Depends on: (none - base table)
    base_orders AS (
        SELECT order_id, customer_id, order_date, amount
        FROM orders
        WHERE status = 'completed'
        AND order_date >= CURRENT_DATE - INTERVAL '1 year'
    ),
    
    -- All active customers
    -- Depends on: (none - base table)
    base_customers AS (
        SELECT customer_id, customer_name, segment, region
        FROM customers
        WHERE status = 'active'
    ),
    
    -- ═══════════════════════════════════════════════
    -- CUSTOMER METRICS CTEs
    -- ═══════════════════════════════════════════════
    
    -- Customer purchase summaries
    -- Depends on: base_orders
    customer_order_metrics AS (
        SELECT 
            customer_id,
            COUNT(*) as order_count,
            SUM(amount) as total_spent,
            MIN(order_date) as first_order,
            MAX(order_date) as last_order
        FROM base_orders
        GROUP BY customer_id
    ),
    
    -- Customer categorization
    -- Depends on: customer_order_metrics
    customer_segments AS (
        SELECT 
            customer_id,
            order_count,
            total_spent,
            CASE 
                WHEN total_spent >= 10000 THEN 'VIP'
                WHEN total_spent >= 1000 THEN 'Regular'
                ELSE 'Occasional'
            END as spend_tier
        FROM customer_order_metrics
    ),
    
    -- ═══════════════════════════════════════════════
    -- REPORT CTEs
    -- ═══════════════════════════════════════════════
    
    -- Final joined report
    -- Depends on: base_customers, customer_segments
    customer_report AS (
        SELECT 
            bc.customer_name,
            bc.segment,
            bc.region,
            cs.order_count,
            cs.total_spent,
            cs.spend_tier
        FROM base_customers bc
        LEFT JOIN customer_segments cs USING (customer_id)
    )
 
-- Main Query
SELECT *
FROM customer_report
ORDER BY total_spent DESC NULLS LAST;

When to Split Queries

If your multi-CTE query exceeds 150-200 lines, or requires more than 10 CTEs, consider: 1) Creating permanent views for commonly-used CTEs, 2) Splitting into multiple queries with temp tables, 3) Using stored procedures to encapsulate stages. CTEs are powerful but not infinitely scalable.

Parallel vs Sequential CTEs

CTEs can be designed as parallel (independent of each other) or sequential (each depending on the previous). Understanding the difference helps you write more efficient queries and enables database optimizers to work more effectively.

Parallel CTEs

•No dependencies on other CTEs
•Can theoretically execute simultaneously
•Combined in the main query via JOIN
•Best when: multiple independent aggregations feed one result
•Example: sales by region, sales by product, sales by month

Sequential CTEs

•Each CTE references a previous one
•Must execute in order
•Forms a transformation pipeline
•Best when: stepwise data refinement
•Example: filter → enrich → aggregate → format

parallel_vs_sequential.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
-- PARALLEL CTEs: Independent branches
WITH 
    -- These three CTEs are independent (parallel)
    sales_by_region AS (
        SELECT region, SUM(amount) as regional_total
        FROM sales GROUP BY region
    ),
    sales_by_category AS (
        SELECT category, SUM(amount) as category_total
        FROM sales GROUP BY category
    ),
    sales_by_quarter AS (
        SELECT DATE_TRUNC('quarter', sale_date) as quarter, SUM(amount) as quarterly_total
        FROM sales GROUP BY DATE_TRUNC('quarter', sale_date)
    )
-- Main query combines parallel branches
SELECT 
    r.region,
    r.regional_total,
    (SELECT SUM(category_total) FROM sales_by_category) as all_categories,
    (SELECT MAX(quarterly_total) FROM sales_by_quarter) as peak_quarter
FROM sales_by_region r;
 
 
-- SEQUENTIAL CTEs: Pipeline transformation
WITH 
    -- Step 1 (no dependency)
    raw_transactions AS (
        SELECT * FROM transactions WHERE date >= '2024-01-01'
    ),
    -- Step 2 (depends on Step 1)
    valid_transactions AS (
        SELECT * FROM raw_transactions WHERE amount > 0 AND status = 'completed'
    ),
    -- Step 3 (depends on Step 2)
    enriched_transactions AS (
        SELECT vt.*, c.customer_tier
        FROM valid_transactions vt
        JOIN customers c USING (customer_id)
    ),
    -- Step 4 (depends on Step 3)
    summarized_transactions AS (
        SELECT customer_tier, SUM(amount) as total, COUNT(*) as count
        FROM enriched_transactions
        GROUP BY customer_tier
    )
SELECT * FROM summarized_transactions;
 
 
-- HYBRID: Parallel branches that converge
WITH 
    -- Parallel branch A
    orders_last_month AS (
        SELECT customer_id, SUM(amount) as monthly_total
        FROM orders
        WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
        AND order_date < DATE_TRUNC('month', CURRENT_DATE)
        GROUP BY customer_id
    ),
    -- Parallel branch B
    orders_same_month_last_year AS (
        SELECT customer_id, SUM(amount) as yoy_total
        FROM orders
        WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '13 months')
        AND order_date < DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 months')
        GROUP BY customer_id
    ),
    -- Convergence point: combines both parallel branches
    customer_comparison AS (
        SELECT 
            COALESCE(lm.customer_id, ly.customer_id) as customer_id,
            COALESCE(lm.monthly_total, 0) as this_month,
            COALESCE(ly.yoy_total, 0) as same_month_last_year
 
        FROM orders_last_month lm
        FULL OUTER JOIN orders_same_month_last_year ly USING (customer_id)
    )
SELECT 
    customer_id,
    this_month,
    same_month_last_year,
    this_month - same_month_last_year as yoy_change
FROM customer_comparison
WHERE this_month > 0 OR same_month_last_year > 0;

Performance Considerations

Multiple CTEs introduce performance considerations that don't exist with simpler queries. Understanding these helps you write efficient multi-CTE statements.

Performance Factors

•Materialization decisions — Each CTE may be inlined or materialized; more CTEs = more optimizer decisions
•Memory pressure — Materialized CTEs consume memory; many large CTEs could exhaust available memory
•Optimization barriers — Materialized CTEs block predicate pushdown from main query into CTE
•Plan complexity — More CTEs = more complex execution plan = longer planning time
•Reuse benefits — CTEs referenced multiple times can amortize expensive computation

performance_techniques.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- TECHNIQUE 1: Minimize CTE width (columns)
-- Bad: Wide CTE that downstream only uses 2 columns
WITH wide_cte AS (
    SELECT * FROM large_table  -- 50 columns
)
SELECT id, name FROM wide_cte;
 
-- Good: Narrow CTE with only needed columns
WITH narrow_cte AS (
    SELECT id, name FROM large_table
)
SELECT id, name FROM narrow_cte;
 
 
-- TECHNIQUE 2: Push filters into earliest possible CTE
-- Bad: Late filtering
WITH all_orders AS (
    SELECT * FROM orders
),
enriched AS (
    SELECT ao.*, c.name FROM all_orders ao JOIN customers c USING (customer_id)
)
SELECT * FROM enriched WHERE order_date >= '2024-01-01';
 
-- Good: Early filtering
WITH recent_orders AS (
    SELECT * FROM orders WHERE order_date >= '2024-01-01'  -- Filter early
),
enriched AS (
    SELECT ro.*, c.name FROM recent_orders ro JOIN customers c USING (customer_id)
)
SELECT * FROM enriched;
 
 
-- TECHNIQUE 3: Control materialization (PostgreSQL 12+)
WITH 
    -- Don't materialize simple filters
    active_users AS NOT MATERIALIZED (
        SELECT user_id FROM users WHERE active = true
    ),
    -- Do materialize expensive aggregations used multiple times
    user_stats AS MATERIALIZED (
        SELECT user_id, COUNT(*) as actions, SUM(duration) as total_time
        FROM user_activity
        WHERE activity_date >= CURRENT_DATE - 30
        GROUP BY user_id
    )
SELECT ...;
 
 
-- TECHNIQUE 4: Avoid unnecessary CTE chains
-- Bad: Trivial CTEs that add overhead
WITH 
    cte1 AS (SELECT * FROM table1),
    cte2 AS (SELECT * FROM cte1 WHERE x > 0),
    cte3 AS (SELECT * FROM cte2)
SELECT * FROM cte3;
 
-- Good: Combine trivial steps
WITH filtered_table AS (
    SELECT * FROM table1 WHERE x > 0
)
SELECT * FROM filtered_table;

Always Check Execution Plans

Don't assume that adding or removing CTEs improves performance. Always compare execution plans (EXPLAIN ANALYZE) before and after changes. The optimizer may produce unexpected results, and behavior varies significantly between database systems.

Summary: Multiple CTEs

We've explored the power of combining multiple CTEs into sophisticated query structures. Let's consolidate the key insights:

Key Takeaways

•Multiple CTEs share one WITH clause — Separate with commas, no comma after the last CTE
•CTEs form dependency graphs — Linear chains, diamonds, and fan-outs serve different purposes
•CTEs can reference earlier CTEs and be referenced multiple times — Enables powerful composition patterns
•Multi-CTE queries are data pipelines — Structure as extraction → transformation → aggregation → presentation
•Manage complexity actively — Group, document, and limit CTEs; split large queries
•Parallel vs sequential has performance implications — Independent CTEs may execute more efficiently

What's Next:

Having mastered multi-CTE composition, we're now ready for the most powerful CTE feature: Recursive CTEs. The next page explores how CTEs can reference themselves to traverse hierarchies, generate series, and solve problems that are impossible with standard SQL.

Page Complete

You now understand how to compose multiple CTEs, manage their dependencies, build data transformation pipelines, and optimize multi-CTE query performance. You're ready to explore recursive CTEs.