Multiple CTEs — Chaining and Reuse

A single WITH clause can define multiple CTEs, each building on the previous ones. This is the core of readable analytical SQL: each CTE represents one well-named logical step, and the final SELECT assembles the result.

Multiple CTEs in one WITH clause

Separate multiple CTEs with commas. Each CTE can reference any CTE defined before it:

WITH
cte_one AS (
    SELECT ...
),
cte_two AS (
    SELECT ... FROM cte_one ...   -- can reference cte_one
),
cte_three AS (
    SELECT ... FROM cte_one      -- can reference cte_one
    JOIN cte_two ON ...          -- can reference cte_two
)
SELECT * FROM cte_three;

The CTEs form a directed acyclic graph (DAG) — each can reference earlier ones, but not later ones (no forward references).

A full multi-CTE pipeline — customer lifetime analysis

WITH
-- Step 1: compute per-order totals
order_totals AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.created_at,
        o.status,
        SUM(oi.quantity * oi.unit_price) AS order_total
    FROM ec_orders o
    JOIN ec_order_items oi ON oi.order_id = o.order_id
    GROUP BY o.order_id, o.customer_id, o.created_at, o.status
),
-- Step 2: summarise per customer (completed ec_orders only)
customer_summary AS (
    SELECT
        customer_id,
        COUNT(*)                            AS order_count,
        MIN(created_at)                     AS first_order,
        MAX(created_at)                     AS last_order,
        SUM(order_total)                    AS lifetime_value,
        AVG(order_total)                    AS avg_order_value
    FROM order_totals
    WHERE status = 'completed'
    GROUP BY customer_id
),
-- Step 3: classify ec_customers into segments
segmented AS (
    SELECT
        customer_id,
        order_count,
        first_order,
        last_order,
        lifetime_value,
        ROUND(avg_order_value::NUMERIC, 2)           AS avg_order_value,
        CASE
            WHEN lifetime_value >= 2000 THEN 'VIP'
            WHEN lifetime_value >= 800  THEN 'Regular'
            ELSE                             'Occasional'
        END                                 AS segment,
        NOW() - last_order                  AS recency
    FROM customer_summary
)
SELECT
    c.name,
    c.country,
    s.segment,
    s.order_count,
    s.lifetime_value,
    s.avg_order_value,
    ROUND(EXTRACT(DAY FROM s.recency))      AS days_since_last_order,
    s.first_order::DATE,
    s.last_order::DATE
FROM segmented s
JOIN ec_customers c ON c.customer_id = s.customer_id
ORDER BY s.lifetime_value DESC;

Four CTEs, four named steps. If you need to debug, you can run each CTE independently (wrap it in WITH ... SELECT * FROM ...) to verify its output.

Purchase this course to unlock the full lesson.

← Previous Next →