Skip to main content
Subqueries & CTEs
4 / 4
0%

Chained CTEs — multiple WITH clauses

0/3 solved3 to unlock next
Prev★ Final lesson

Lesson 04

Chained CTEs — multiple WITH clauses

Lesson Description

Build multi-stage analysis pipelines with chained CTEs to handle complex business logic cleanly. You will learn how to validate each stage and produce a final result that is easy to maintain.

Easy Project

Mini project: Build a 3-step CTE pipeline that isolates top customers, calculates spend metrics, and returns a clean final view.

Building a query as a pipeline

In the CTEs lesson you wrote one named step and then a main query. Real reporting questions often need three or four steps, each building on the last. Take this one from the store database: "How many of our customers are one-time buyers, and how many come back?" To answer it you need to count delivered orders per customer, then label each customer with a tier based on that count, and then count customers per tier. That's three distinct steps, and each one needs the result of the step before.

Chained CTEs let you write that as a pipeline. You list several CTEs in a single WITH clause, separated by commas, and each CTE can read from any CTE defined above it. The final SELECT reads from whichever step it needs. The result is a query you can read like a recipe: first this, then this, then this.

Syntax

WITH
  step_one AS (
    SELECT ...
    FROM real_table
  ),
  step_two AS (
    SELECT ...
    FROM step_one
  ),
  step_three AS (
    SELECT ...
    FROM step_two
    JOIN another_table ON ...
  )
SELECT ...
FROM step_three;

The rules:

  • One WITH keyword at the top, never repeated.
  • A comma between CTEs, and no comma after the last one.
  • A CTE can reference CTEs defined above it, plus any real table. It cannot reference one defined below it.
  • The main query can reference any of the CTEs, not just the last.

Worked examples

1. Count, label, summarise

Here is the customer tiers question as three steps:

WITH
  customer_orders AS (
    SELECT customer_id, COUNT(*) AS delivered_orders
    FROM orders
    WHERE status = 'delivered'
    GROUP BY customer_id
  ),
  customer_tiers AS (
    SELECT customer_id,
           delivered_orders,
           CASE
             WHEN delivered_orders >= 3 THEN 'loyal'
             WHEN delivered_orders = 2  THEN 'returning'
             ELSE 'one-time'
           END AS tier
    FROM customer_orders
  )
SELECT tier,
       COUNT(*)              AS customers,
       SUM(delivered_orders) AS orders
FROM customer_tiers
GROUP BY tier
ORDER BY customers DESC;

The result has 2 rows: 13 returning customers who account for 26 delivered orders, and 7 one-time customers with 7 orders. That covers all 33 delivered orders. Notice what's missing: there's no loyal row, because no customer has three or more delivered orders. GROUP BY only produces groups that have rows. If your report must always show all three tiers, you'd need a list of tiers to LEFT JOIN from, which is a good exercise in its own right.

Splitting the work this way makes each step easy to check. The CASE logic lives in one place, separate from the counting and the summary.

2. A CTE that reads another CTE for a benchmark

In the hospital database, "Which doctors carry an above-average appointment load?" You need each doctor's count, and then the average of those counts:

WITH
  doctor_load AS (
    SELECT doctor_id, COUNT(*) AS appointments
    FROM appointments
    WHERE status <> 'cancelled'
    GROUP BY doctor_id
  ),
  avg_load AS (
    SELECT AVG(appointments) AS avg_appts
    FROM doctor_load
  )
SELECT d.first_name, d.last_name, d.specialty,
       dl.appointments,
       ROUND(al.avg_appts, 1) AS avg_appts
FROM doctor_load dl
JOIN doctors d ON d.id = dl.doctor_id
CROSS JOIN avg_load al
WHERE dl.appointments > al.avg_appts
ORDER BY dl.appointments DESC, d.last_name;

The average is 4.6 appointments per doctor, and 5 doctors are above it: Sarah Johnson, Aisha Khan, Priya Patel and Yuki Tanaka with 8 each, and Olivia Bennett with 7. avg_load has exactly one row, so the CROSS JOIN simply attaches that one number to every doctor row, which makes it available in both WHERE and SELECT.

There's a subtle choice baked into this query. doctor_load only has rows for doctors with at least one non-cancelled appointment, which is 14 of the 15 doctors. Marcus Wright, who has none, isn't part of the average. If you wanted him counted as a zero, you'd start doctor_load from doctors with a LEFT JOIN to appointments, and the average would drop. Neither version is wrong, but you should choose deliberately.

3. Aggregate, then compare each row with the previous one

Chained CTEs pair naturally with window functions, which you'll study in depth in the next course. Here's a month-over-month revenue report for the store, excluding cancelled orders:

WITH
  monthly_revenue AS (
    SELECT DATE_TRUNC('month', o.order_date)::date AS month,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    WHERE o.status <> 'cancelled'
    GROUP BY 1
  ),
  with_prev AS (
    SELECT month,
           revenue,
           LAG(revenue) OVER (ORDER BY month) AS prev_revenue
    FROM monthly_revenue
  )
SELECT month,
       revenue,
       prev_revenue,
       revenue - prev_revenue AS change,
       ROUND(100.0 * (revenue - prev_revenue) / prev_revenue, 1) AS pct_change
FROM with_prev
ORDER BY month;

This returns 5 rows, January to May 2025. January has 918.72 in revenue and NULL for everything comparative, because there's no earlier month. February drops 17.5% to 758.39, March more than doubles to 1,523.86 (up 100.9%), April edges up 6.1% to 1,616.74, and May falls 19.8% to 1,296.51. The with_prev step exists so the final SELECT can refer to prev_revenue by name three times, instead of repeating LAG(revenue) OVER (ORDER BY month) in every expression.

4. Two steps on the HR data

"For each job title, where do employees sit within the official salary band?" The jobs table holds min_salary and max_salary, so the first step computes a position for each employee (0% at the bottom of the band, 100% at the top), and the second summarises by title:

WITH
  emp_band AS (
    SELECT j.title,
           ROUND(100.0 * (e.salary - j.min_salary)
                 / NULLIF(j.max_salary - j.min_salary, 0), 1) AS band_pct
    FROM employees e
    JOIN jobs j ON j.id = e.job_id
  ),
  band_summary AS (
    SELECT title,
           COUNT(*)               AS employees,
           ROUND(AVG(band_pct), 1) AS avg_band_pct,
           MAX(band_pct)          AS max_band_pct
    FROM emp_band
    GROUP BY title
  )
SELECT *
FROM band_summary
ORDER BY avg_band_pct DESC NULLS LAST
LIMIT 5;

Accountants come out on top: 5 employees averaging 77.5% of the way up their band, with at least one at exactly 100%. Next are Marketing Manager (66.7%), Sales Representative (52.1% across 4 people), HR Representative (50.0%) and Finance Manager (48.7%). NULLIF guards against a band where minimum and maximum are equal, which would otherwise be a division by zero.

Under the hood: how the chain is executed

Chaining doesn't mean PostgreSQL runs each step to completion and stores it. As you saw in the previous lesson, a CTE referenced once is normally inlined, so the planner sees the whole chain as one big query and optimises it as a unit. A CTE referenced from two places (like doctor_load in example 2, which is read by avg_load and by the main query) is materialized once and then read twice, so you don't pay for the aggregation two times.

Because the planner sees the whole chain, splitting a query into five readable CTEs costs nothing compared with one dense query that does the same work. Optimise for the person who reads it next.

Debugging a chain

The most practical benefit of chaining is that you can inspect any step. Temporarily replace the main query with SELECT * FROM customer_orders; (or any other CTE name) and run it. The later CTEs are simply ignored when nothing references them. Once that step looks right, restore the real final query.

Common mistakes

Referencing a CTE before it's defined

WITH
  top_depts AS (
    SELECT * FROM dept_stats WHERE avg_sal > 9000
  ),
  dept_stats AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
  )
SELECT * FROM top_depts;

PostgreSQL rejects this with relation "dept_stats" does not exist, and helpfully adds There is a WITH item named "dept_stats", but it cannot be referenced from this part of the query. The fix is to put dept_stats first. Order your CTEs the way you'd explain the logic out loud.

Repeating the WITH keyword

WITH a AS (SELECT 1 AS x),
WITH b AS (SELECT 2 AS y)
SELECT * FROM a, b;

This is a syntax error at or near "WITH". There is one WITH per statement; the second CTE follows a comma only: WITH a AS (...), b AS (...).

Trying to filter on a window function in the same step

A frequent reason to chain is ranking. This looks reasonable but fails:

SELECT department_id,
       AVG(salary) AS avg_salary,
       RANK() OVER (ORDER BY AVG(salary) DESC) AS rnk
FROM employees
GROUP BY department_id
HAVING RANK() OVER (ORDER BY AVG(salary) DESC) <= 3;

PostgreSQL reports window functions are not allowed in HAVING (and the same for WHERE), because window functions are calculated after filtering. The fix is exactly what this lesson teaches: compute the rank in one CTE, then filter on rnk in the next step or in the main query.

How other databases differ

Multiple CTEs in one WITH clause work the same way in SQL Server, SQLite and MySQL 8.0 or later, including the rule that a CTE can only see the ones above it. None of them let you repeat the WITH keyword within a statement.

Practice

The exercises below ask you to:

  • Rank departments by average salary with two CTEs, one for the statistics and one for the ranking.
  • Find the top three product categories by revenue, again with a statistics step and a ranking step.
  • Find the top five doctors by appointment count. Think about what should happen when doctors tie, and how your ordering keeps the output stable.

Build each chain one step at a time, running SELECT * FROM the newest CTE before you add the next.

Key takeaways

  • Chained CTEs are several named steps in one WITH clause, separated by commas, with no comma before the main query.
  • Each CTE can read real tables and any CTE defined above it, never one below it.
  • The main query can use any CTE in the chain, and you can debug a chain by selecting from one step at a time.
  • Chaining is how you filter on values that can't be filtered directly, such as window function results.
  • PostgreSQL optimises the chain as a whole, so readable steps cost nothing extra.

Chained CTEs — multiple WITH clauses: in depth

Several CTEs can build on each other, each adding one transformation: clean the data, aggregate it, rank it, then pick the answer. This is how analysts write long queries that stay reviewable, because every step has a name and can be checked on its own.

Worked examples

Top customer per country, step by step

WITH spend AS (
  SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.customer_id
),
ranked AS (
  SELECT c.country, c.first_name, c.last_name, s.total,
         RANK() OVER (PARTITION BY c.country ORDER BY s.total DESC) AS rnk
  FROM spend s
  JOIN customers c ON c.id = s.customer_id
)
SELECT country, first_name, last_name, total
FROM ranked
WHERE rnk = 1
ORDER BY total DESC;

spend aggregates, ranked orders customers within each country, and the final query keeps the winners.

Debugging one step

WITH spend AS (
  SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.customer_id
)
SELECT * FROM spend ORDER BY total DESC LIMIT 5;

Replacing the final SELECT with a look at one CTE is the fastest way to check an intermediate result.

Common mistakes

  • Referencing a CTE before it is defined. Each CTE can only use the ones listed above it.
  • Filtering on a window function in the same step that computes it. WHERE rnk = 1 must go in a later step, because window functions run after WHERE.
  • Repeating the WITH keyword for each CTE. Write WITH once and separate the CTEs with commas.

Other databases

  • Chained CTEs work the same way in PostgreSQL, SQL Server, Oracle, SQLite and MySQL 8.0+.
  • Recursive CTEs (WITH RECURSIVE) extend this to hierarchies; SQL Server omits the RECURSIVE keyword.
Check yourself: Why can't the ranking and the rnk = 1 filter live in the same CTE?

Window functions are computed after WHERE, so the rank does not exist yet when WHERE runs. Filter in the next step.

Concept check

Quick check: Chained CTEs — multiple WITH clauses

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Department salary ranking with two CTEs

Enterprise Workforce AnalyticseasyPostgreSQL

Write two CTEs: dept_stats (avg salary and headcount per department) and ranked_depts (rank departments by avg salary descending). Return department name, avg_salary, and emp_count for all departments, ordered by rank.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Top 3 product categories by revenue (two CTEs)

Commerce Operations IntelligenceeasyPostgreSQL

Use two CTEs: category_revenue (total revenue per category from order_items + products) and ranked_cats (rank by revenue descending). Return the top 3 category name and revenue, ordered by rank.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Top 5 doctors by appointment count (two CTEs)

Clinical Care PerformanceeasyPostgreSQL

Use two CTEs: doc_stats (appointment count per doctor) and ranked_docs (rank doctors by appointment count descending). Return the top 5 doctor first_name, last_name, and total_appointments, ordered by rank.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Final checkpoint

End-of-course high-level quiz

Pass mark: 80%. Review each explanation after you submit.

1. Which clause should you use to filter grouped results?

2. What is the safest way to avoid accidental row multiplication in joins?

3. Which feature is best for ranking rows within partitions?

4. Why should final analytical queries usually include ORDER BY?

5. When query logic gets complex, what improves readability most?

Subqueries & CTEs
4 / 4
0%

Chained CTEs — multiple WITH clauses

0/3 solved3 to unlock next
Prev★ Final lesson