Module 4: Subqueries and Common Table Expressions ⏱ 19 min

Chaining Multiple CTEs

By the end of this lesson you will be able to:
  • Define multiple CTEs in a single WITH clause
  • Reference earlier CTEs from later ones to build analytical pipelines
  • Decompose complex questions into simple, named stages

A single CTE cleans up a query. Multiple CTEs in sequence can decompose a genuinely hard question into simple steps that fit together like Lego blocks. Each stage computes one clear thing, names it, and hands it to the next stage. The final query is a short, readable composition of those named blocks.

Chaining CTEs is how analysts build real-world pipelines: first clean the data, then aggregate it, then rank it, then filter to the top tier. Every stage is testable on its own, and the names document what each piece means. When a bug appears, you know exactly which stage to inspect.

In this lesson you will write queries with two, three, and even four CTEs, and you will see how order matters — a later CTE can reference an earlier one, but never the reverse.

flowchart TD
  C1["WITH a AS (...)"] --> C2[", b AS (SELECT ... FROM a)"]
  C2 --> C3[", c AS (SELECT ... FROM b)"]
  C3 --> F["SELECT * FROM c"]
  style C1 fill:#0e7490,color:#fff
  style C2 fill:#0e7490,color:#fff
  style C3 fill:#0e7490,color:#fff
  style F fill:#1e293b,color:#fff
A chain of CTEs: each block references only the ones above it, forming a directed pipeline.

Syntax: comma-separated CTEs

After WITH, list each CTE separated by a comma. Every CTE has the same name AS (SELECT ...) shape. The first CTE reads from real tables; later ones can read from real tables and from any CTE defined above them.

WITH company_avg AS (
    SELECT AVG(salary) AS avg_sal FROM employees
),
high_earners AS (
    SELECT name, salary
    FROM employees
    WHERE salary > (SELECT avg_sal FROM company_avg)
)
SELECT * FROM high_earners;

Notice the comma after company_avg. Without it, the parser thinks the statement ended and treats high_earners as an error. This comma is the only new syntax — everything else is the CTE behaviour you already know.

Two CTEs: one computes the company average, and the second selects employees who beat it.
WITH company_avg AS (
  SELECT AVG(salary) AS avg_sal FROM employees
),
high_earners AS (
  SELECT name, salary
  FROM employees
  WHERE salary > (SELECT avg_sal FROM company_avg)
)
SELECT * FROM high_earners;

Building a three-stage pipeline

Real analysis usually needs more than two stages. Consider the question: 'Show me every department's name, its average salary, and its total project budget.' Those three facts live in three different places, and the cleanest way to assemble them is three CTEs.

The first CTE aggregates salaries by department. The second aggregates project budgets by department. The third is not needed here — the main query simply joins both CTEs to the departments table. But if you also wanted to flag departments whose average salary was above the company mean, you would add a third CTE for that threshold, and a fourth for the final filtered list.

The discipline is the same at every scale: one named computation per stage, then a short final query that composes them.

Three CTEs: department salary stats, project budget stats, and a final join that presents both side by side.
WITH dept_salary AS (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
),
dept_project AS (
  SELECT department_id, SUM(budget) AS total_budget
  FROM projects
  GROUP BY department_id
)
SELECT d.name, ds.avg_sal, dp.total_budget
FROM departments d
LEFT JOIN dept_salary ds ON d.id = ds.department_id
LEFT JOIN dept_project dp ON d.id = dp.department_id;
flowchart LR
  A["CTE a"] --> B["CTE b references a"]
  B --> C["CTE c references b"]
  C -.-x|"illegal"| A
  style A fill:#0e7490,color:#fff
  style B fill:#0e7490,color:#fff
  style C fill:#0e7490,color:#fff
Later CTEs can reference earlier ones, but the reverse is illegal — order is enforced.

When to chain versus when to merge

Not every multi-CTE query needs three stages. If two aggregates share the same source table and grouping key, merging them into one CTE with multiple aggregates is simpler. The example below puts AVG(salary) and COUNT(*) in the same CTE because both read employees grouped by department_id.

Split into separate CTEs only when the sources, filters, or groupings differ. The goal is clarity, not maximising the CTE count. A pipeline with one well-named CTE is better than three trivial ones that confuse more than they help.

The exercises that follow ask you to build pipelines of increasing depth. Resist the temptation to cram everything into a single SELECT — that is exactly the nesting habit CTEs are meant to cure.

A single CTE can compute multiple aggregates when they share the same source and grouping.
WITH dept_stats AS (
  SELECT department_id,
    AVG(salary) AS avg_sal,
    COUNT(*) AS headcount
  FROM employees
  GROUP BY department_id
)
SELECT d.name, ds.avg_sal, ds.headcount
FROM departments d
LEFT JOIN dept_stats ds ON d.id = ds.department_id;
Exercise

Write a query with two CTEs. The first, company_avg, should compute the overall average salary. The second, high_earners, should select the name and salary of every employee who earns more than that average. Return all columns from high_earners.

WITH company_avg AS (
  -- compute overall average
),
high_earners AS (
  -- select above-average employees
)
SELECT * FROM high_earners;
Exercise

Write a query with three CTEs: dept_salary (department id and average salary), dept_project (department id and total project budget), and a final SELECT joining both to departments. Return the department name, avg_sal, and total_budget.

WITH dept_salary AS (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
),
dept_project AS (
  -- total budget per department
)
SELECT d.name, ds.avg_sal, dp.total_budget
FROM departments d
LEFT JOIN dept_salary ds ON d.id = ds.department_id
LEFT JOIN dept_project dp ON d.id = dp.department_id;
Exercise

This query tries to define final before above_avg, but a CTE cannot reference a later CTE. Reorder the CTEs so the dependency flows downward: dept_avg first, then above_avg, then final.

WITH dept_avg AS (
  SELECT d.name, AVG(e.salary) AS avg_sal
  FROM employees e
  JOIN departments d ON e.department_id = d.id
  GROUP BY d.name
),
final AS (
  SELECT * FROM above_avg
),
above_avg AS (
  SELECT * FROM dept_avg WHERE avg_sal > 600000
)
SELECT * FROM final;
Exercise

Find departments that have both an above-average salary and at least one active project with budget over 500000. Use three CTEs: dept_avg_salary (department id and average salary), active_big_projects (department ids with qualifying projects), and company_avg (overall average). Join them in the final SELECT and return the department name.

WITH dept_avg_salary AS (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
),
active_big_projects AS (
  SELECT department_id
  FROM projects
  WHERE status = 'active' AND budget > 500000
),
company_avg AS (
  SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT d.name
FROM departments d
JOIN dept_avg_salary das ON d.id = das.department_id
JOIN active_big_projects abp ON d.id = abp.department_id
CROSS JOIN company_avg ca
WHERE das.avg_sal > ca.avg_sal;
Exercise

In a chain of CTEs, which references are legal?

Recap

  • Multiple CTEs are listed in one WITH clause, separated by commas.
  • A CTE can reference earlier CTEs and real tables, but never later ones — order is enforced.
  • Chain CTEs when stages differ in source, filter, or grouping; merge aggregates into one CTE when they share the same logic.
  • Named stages make complex queries readable, testable, and debuggable.

With subqueries, CTEs, and recursive CTEs in your toolkit, you can break almost any analytical problem into small, reusable, readable building blocks.

Checkpoint quiz

What separates multiple CTEs in a single WITH clause?

Can a CTE defined third in the list reference the first CTE?

Go deeper — technical resources