SQL Interview Prep · Lesson

Chaining Multiple CTEs

Building a pipeline of named steps that reference each other.

Chaining Multiple CTEs is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Chain CTEs

Real interview problems rarely fit in one step. Chaining CTEs lets you build a pipeline of named stages, where each stage transforms the output of the previous one. This mirrors how a senior engineer decomposes a hard query into manageable pieces.

Instead of nesting subqueries three levels deep, you write each step once, give it a name, and let later steps reference it.

The Comma-Separated Syntax

To define several CTEs you write WITH once, then separate each named block with a comma. You do not repeat the WITH keyword.

One WITH at the top.
A comma between each CTE definition.
No comma before the final main query.

WITH a AS (
    SELECT customer_id FROM orders
),
b AS (
    SELECT customer_id FROM a
)
SELECT *
FROM b;

Later CTEs Can Reference Earlier Ones

The power of chaining: a CTE can read from any CTE defined before it. This forward-only visibility lets you build a dependency chain.

An earlier CTE cannot see a later one, so order matters. Arrange your stages from raw data toward the final shape.

WITH filtered AS (
    SELECT *
    FROM events
    WHERE event_type = 'purchase'
),
per_user AS (
    SELECT user_id, COUNT(*) AS purchases
    FROM filtered
    GROUP BY user_id
)
SELECT *
FROM per_user;

Worked Example: Three-Stage Pipeline

Question: among customers who spent over 1000 dollars, what is the average spend? Break it into three stages: total spend per customer, filter the big spenders, then average them.

Each CTE name documents its purpose, so a reviewer instantly understands the flow.

WITH spend AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
),
big_spenders AS (
    SELECT customer_id, total
    FROM spend
    WHERE total > 1000
)
SELECT AVG(total) AS avg_big_spend
FROM big_spenders;

Order of Definition Matters

Because visibility is forward-only, a CTE that depends on another must be listed after its dependency. If you reference a name that has not been defined yet, the database raises a 'relation does not exist' error.

A good habit: read your CTE list top to bottom and confirm each name used has already appeared above it.

Referencing One CTE From Multiple Others

A single CTE can feed several downstream CTEs. This is where chaining beats nested subqueries: you compute a base result once and branch from it.

Here both active and recent read from base, avoiding duplicated logic.

WITH base AS (
    SELECT * FROM users WHERE deleted = false
),
active AS (
    SELECT id FROM base WHERE last_login > NOW() - INTERVAL '7 days'
),
recent AS (
    SELECT id FROM base WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT (SELECT COUNT(*) FROM active) AS active_cnt,
       (SELECT COUNT(*) FROM recent) AS recent_cnt;

Joining Two CTEs Together

Chained CTEs are frequently joined in the main query. Compute each side separately, then combine. This keeps each calculation isolated and the join trivial.

Below we compute order counts and refund counts independently, then join them per customer.

WITH orders_cte AS (
    SELECT customer_id, COUNT(*) AS orders
    FROM orders GROUP BY customer_id
),
refunds_cte AS (
    SELECT customer_id, COUNT(*) AS refunds
    FROM refunds GROUP BY customer_id
)
SELECT o.customer_id, o.orders, COALESCE(r.refunds, 0) AS refunds
FROM orders_cte o
LEFT JOIN refunds_cte r ON r.customer_id = o.customer_id;

Readability Over Nesting

Compare a three-level nested subquery to a three-CTE pipeline. The nested version forces a reader to mentally unwrap from the inside out. The CTE version reads in execution order, top to bottom.

Interviewers reward the CTE approach because it is what they would want to maintain in production. Naming each stage is documentation that never goes stale.

A Common Chaining Mistake

Beginners often add a comma after the last CTE, right before the main SELECT. That trailing comma is a syntax error.

Commas go between CTE definitions only.
The final closing parenthesis is followed directly by the main query, no comma.

Another trap: forgetting that each CTE needs its own complete SELECT inside the parentheses.

Does Each Stage Run Separately?

A nuanced interview point: logically the pipeline reads as discrete steps, but the optimizer may inline and fuse them into one execution plan. You are not forced to pay for intermediate materialization in most engines.

So chaining helps you reason about the query without necessarily costing performance. Mention this to show depth.

Naming Stages Like a Pipeline

Good stage names turn a query into self-documenting code. Prefer names that describe the output of each step, not the operation.

spend and big_spenders beat step1 and step2.
A reader should infer the whole flow from the CTE names alone.
Consistent naming across stages makes the join in the main query obvious.

In an interview, naming stages clearly signals you write maintainable production SQL.

Quick Check

Check your grasp of how chained CTEs reference each other.

Recap: Chaining CTEs

You learned to build pipelines: one WITH, comma-separated CTE definitions, and forward-only visibility where each stage can read earlier stages.

Order CTEs from raw data toward the final result.
Reuse a base CTE in several downstream steps.
No trailing comma before the main query.
Chaining aids readability without necessarily hurting performance.

Next: comparing CTEs to subqueries and temp tables.

Frequently asked questions

Is the “Chaining Multiple CTEs” lesson free?

Yes — the full text of “Chaining Multiple CTEs” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Chaining Multiple CTEs”?

Building a pipeline of named steps that reference each other. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Chaining Multiple CTEs” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep