Breaking a query into steps with WITH

Name each step of a hard question, then read the query top to bottom like a recipe.

Lesson 23 put queries inside other queries. That works, but nested parentheses get hard to read fast. A CTE (common table expression) lifts each step out to the top, gives it a name, and lets the main query use that name like a table.

The shape

WITH rep_totals AS (
    SELECT owner_id, SUM(amount) AS pipeline
    FROM opportunities
    WHERE closed_at IS NULL
    GROUP BY owner_id
)
SELECT AVG(pipeline) AS avg_rep_pipeline
FROM rep_totals;

That’s lesson 23’s per-rep average, $206,500, rewritten. Compare the two: same answer, but the CTE version reads top to bottom. First build rep_totals, then average it.

A CTE only exists while its query runs. It isn’t saved anywhere.

Several steps

Separate steps with a comma, and write WITH only once. A later step can use an earlier one:

WITH won AS (
    SELECT owner_id, SUM(amount) AS won_amount
    FROM opportunities
    WHERE is_won = 1
    GROUP BY owner_id
),
open_pipeline AS (
    SELECT owner_id, SUM(amount) AS open_amount
    FROM opportunities
    WHERE closed_at IS NULL
    GROUP BY owner_id
)
SELECT u.name,
       COALESCE(w.won_amount, 0)  AS won,
       COALESCE(p.open_amount, 0) AS still_open
FROM users AS u
LEFT JOIN won           AS w ON w.owner_id = u.user_id
LEFT JOIN open_pipeline AS p ON p.owner_id = u.user_id
ORDER BY u.name;

Each step aggregates on its own, so the final join is one row per rep on each side. That’s the safe way to combine two different totals, and it avoids the row-multiplying fan-out trap covered in lesson 28.

Using a step twice

A step can be used more than once. Here rep_pipeline feeds the main query and the average it’s compared to:

WITH rep_pipeline AS (
    SELECT owner_id, SUM(amount) AS pipeline
    FROM opportunities
    WHERE closed_at IS NULL
    GROUP BY owner_id
)
SELECT owner_id, pipeline
FROM rep_pipeline
WHERE pipeline > (SELECT AVG(pipeline) FROM rep_pipeline);

Without a CTE, you’d write that per-rep GROUP BY twice and have to keep both copies in sync.

Building it one step at a time

CTEs make debugging easy. Write the first step, then test it on its own by selecting straight from it:

WITH rep_pipeline AS (
    SELECT owner_id, SUM(amount) AS pipeline
    FROM opportunities
    WHERE closed_at IS NULL
    GROUP BY owner_id
)
SELECT * FROM rep_pipeline;   -- check this step before adding the next

Once it looks right, replace the last line with the real query. When a number in the final result looks wrong, point that last SELECT at each step in turn until you find where it went wrong.

Subquery or CTE?

They usually run the same. CTEs are about readability, not speed.

Common mistakes

Dialect notes

Snowflake notes

CTEs are standard in Snowflake, and they’re the backbone of dbt: a dbt model is usually a chain of CTEs (an import step per source table, a few logic steps, then a final SELECT). Snowflake decides how to run each CTE, so splitting a query into steps doesn’t slow it down.

More in the field guide: CTE (WITH) and the WITH breakdown. Exercise I5 is the Try it below, with an extra twist.

Try it

Which reps have more open pipeline than the average rep? Leave out deals with no owner. Return each rep’s name and open pipeline, largest first.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson