Chaining Several CTEs

Define several CTEs in one WITH, each building on the last, to express a query as ordered steps.

SyntaxWITH first AS (subquery), second AS (SELECT ... FROM first), third AS (SELECT ... FROM second) SELECT ... FROM third

One WITH clause can define any number of CTEs, separated by commas. Each one may reference the CTEs defined before it, which turns a query into a pipeline: filter, then aggregate, then rank, then join — each stage named and each stage readable on its own.

One WITH, commas between

The keyword appears once. WITH a AS (...) WITH b AS (...) is a syntax error; WITH a AS (...), b AS (...) is the correct form. Names must be unique within that WITH.

Order matters

Forward references are not allowed: a CTE can reference only CTEs that appear earlier in the same clause. That restriction is what rules out mutually recursive CTEs. In practice it means you write the steps in dependency order, which is also the order you would explain them to a colleague.

You are not limited to one WITH per statement — an inner query block gets its own. Name resolution starts in the current query block and works outward, so an inner block can reference a CTE defined at an enclosing level, but a CTE defined inside a subquery is invisible to the blocks outside it.

What trips people up

Chaining reads like assignment, but it is not procedural. MySQL may merge each CTE into the query that uses it, so a five-step chain is often executed as one flat join, and a step that filters nothing costs nothing. It also means a step you expected to run once may be re-evaluated per reference unless it is materialized.

Example

Example · sql
-- Signup -> first order -> repeat order funnel for one cohort month.
WITH cohort AS (
    SELECT id AS customer_id, created_at
    FROM customers
    WHERE created_at >= '2026-01-01'
      AND created_at <  '2026-02-01'
),
orders_in_cohort AS (                       -- step 2 reads step 1
    SELECT o.customer_id, o.id AS order_id, o.placed_at, o.total
    FROM orders AS o
    JOIN cohort AS c ON c.customer_id = o.customer_id
),
per_customer AS (                           -- step 3 reads step 2
    SELECT
        customer_id,
        COUNT(*)         AS order_count,
        MIN(placed_at)   AS first_order_at,
        SUM(total)       AS lifetime_value
    FROM orders_in_cohort
    GROUP BY customer_id
)
SELECT
    COUNT(*)                                          AS signups,
    COUNT(pc.customer_id)                             AS ordered_at_least_once,
    SUM(pc.order_count >= 2)                          AS ordered_twice,
    ROUND(AVG(COALESCE(pc.lifetime_value, 0)), 2)     AS avg_ltv
FROM cohort AS c
LEFT JOIN per_customer AS pc ON pc.customer_id = c.customer_id;

When to use it

  • Express a funnel or cohort report as filter, join, aggregate, compare.
  • Reuse one filtered base set across two later aggregation steps.
  • Break a 200-line reporting query into stages a reviewer can read one at a time.
  • Stage a transformation before an INSERT ... SELECT into a reporting table.

More examples

The nested longhand the chain replaces

The nested version is what a chained WITH desugars to. Reading it inside out is the skill you need for other people's code; writing the chained version is what you should do for your own.

Example · sql
-- Longhand: the same three steps, nested inside out.
SELECT customer_id, order_count
FROM (
    SELECT customer_id, COUNT(*) AS order_count
    FROM (
        SELECT o.customer_id, o.id
        FROM orders AS o
        JOIN (
            SELECT id AS customer_id FROM customers WHERE created_at >= '2026-01-01'
        ) AS cohort ON cohort.customer_id = o.customer_id
    ) AS oic
    GROUP BY customer_id
) AS per_customer
WHERE order_count >= 2;

-- Shorthand: same steps, top to bottom.
WITH cohort AS (
    SELECT id AS customer_id FROM customers WHERE created_at >= '2026-01-01'
),
oic AS (
    SELECT o.customer_id, o.id FROM orders AS o JOIN cohort USING (customer_id)
),
per_customer AS (
    SELECT customer_id, COUNT(*) AS order_count FROM oic GROUP BY customer_id
)
SELECT customer_id, order_count FROM per_customer WHERE order_count >= 2;

Reusing one step in two later steps

A step used by two later steps is the case where CTEs genuinely beat derived tables: with a derived table you would have to repeat the `discontinued_at IS NULL` filter in both places, and the two copies drift apart the first time the rule changes.

Example · sql
WITH active_skus AS (
    SELECT id, category_id, price
    FROM products
    WHERE discontinued_at IS NULL
),
category_avg AS (
    SELECT category_id, AVG(price) AS avg_price
    FROM active_skus                      -- first use
    GROUP BY category_id
)
SELECT p.id, p.price, ca.avg_price
FROM active_skus AS p                     -- second use
JOIN category_avg AS ca ON ca.category_id = p.category_id
WHERE p.price > ca.avg_price * 1.5;

A CTE at an inner level

Shows that WITH is per query block, not per statement. The outer CTE is visible inside the subquery, but `recent_shipments` cannot be referenced from the outer block — useful when a subquery needs its own scratch step without polluting the top-level names.

Example · sql
-- Each level gets its own WITH; the inner one is invisible outside its block.
WITH regions AS (
    SELECT id, name FROM regions WHERE active = 1
)
SELECT r.name, dt.shipped
FROM regions AS r
JOIN (
    WITH recent_shipments AS (
        SELECT region_id, COUNT(*) AS shipped
        FROM shipments
        WHERE shipped_at >= CURDATE() - INTERVAL 7 DAY
        GROUP BY region_id
    )
    SELECT * FROM recent_shipments
) AS dt ON dt.region_id = r.id;

Related

sql withsql derived tablessql cte readabilitysql cte materializationsql group by

Discussion

  • Be the first to comment on this lesson.