Tier 3 · Intermediate

Chained CTEs in SQL

By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17

Before this Common Table Expressions (CTEs)

Builds toward Recursive CTEs, Multi-CTE Query Architecture, Sessionization and Funnel Analysis Patterns

What are Chained CTEs in SQL?

Chained CTEs let you break a complex query into named layers, each doing one thing, in the order you read them.

You're calculating average monthly spend per user plan type. You need to filter to active users, aggregate each user's charges, then join the two results and aggregate again. That's three layers of logic. Written as nested subqueries, the innermost layer executes first but appears deepest in the code — you read it inside-out. With chained CTEs, each layer gets a name and sits in reading order.

How do you chain multiple CTEs in one query?

The WITH clause holds all the definitions, separated by commas. Think of each CTE as a named step. The main query at the bottom reads from whichever step produces the final result. Names flow forward — each CTE can reference any CTE defined before it in the list, but not after it.

WITH active_users AS (
  SELECT user_id, plan
  FROM users
  WHERE status = 'active'
),
monthly_spend AS (
  SELECT user_id, SUM(amount) AS total
  FROM charges
  GROUP BY user_id
),
summary AS (
  SELECT a.plan, AVG(m.total) AS avg_spend
  FROM active_users a
  JOIN monthly_spend m ON a.user_id = m.user_id
  GROUP BY a.plan
)
SELECT *
FROM summary

active_users filters the users table. monthly_spend aggregates charges. summary joins the two and aggregates again. The main query reads from summary. Each layer is narrow enough to understand in isolation.

Can one CTE reference another CTE?

Order matters. If you put summary before monthly_spend, PostgreSQL errors — monthly_spend hasn't been defined yet. PostgreSQL reads the WITH clause top to bottom, and a name is only visible after it appears.

How do you debug a multi-step SQL query?

The debugging advantage is significant. When a chained CTE query returns wrong results, you don't have to guess which step failed. Comment out the main SELECT and add SELECT * FROM active_users instead. Run it. Inspect the intermediate result. Add the next CTE back and repeat. Each step is independently queryable.

WITH high_value_orders AS (
  SELECT customer_id, SUM(total_amount) AS total
  FROM orders
  GROUP BY customer_id
  HAVING SUM(total_amount) > 500
),
customer_summary AS (
  SELECT c.name, hvo.total
  FROM customers c
  JOIN high_value_orders hvo ON c.id = hvo.customer_id
)
SELECT name, total
FROM customer_summary
ORDER BY total DESC

When should you use chaining vs a single CTE? A single CTE makes sense when you have one intermediate result and one main query. Chaining makes sense when the query has multiple distinct stages, when the same intermediate result feeds into more than one downstream CTE, or when the logic is complex enough that naming each stage makes the overall structure easier to review. There is no rule that says a query must have a certain number of CTEs — use as many as the problem calls for, and no more.

Do chained CTEs run in parallel in PostgreSQL?

The one thing that trips people up: chained CTEs do not run in parallel.

PostgreSQL executes the whole query as a single plan. CTEs in the chain that have no dependency on each other are still run sequentially. The chain is a logical structure for the analyst's benefit, not an instruction to the database to parallelize work. If you need two independent aggregations, PostgreSQL still processes them one at a time.

Practice Chained CTEs in SQL

Practice · easy ecommerce · Brightlane

Brightlane's catalog team wants to identify product categories with a high average price.

Write a query to return the category ID and average price for every category whose average product price exceeds $300.

Assumptions:

The products table has one row per product with a category_id and a price.
A category's average price is the average of every product's price linked to that category_id.
Only categories whose average price exceeds $300 should appear.

Output:

One row per qualifying category, with columns category_id and avg_price.

Schema · ecommerce5 tables? = nullable

9 Chained CTEs practice problems

Write a query to return the category ID and average price for every category whose average product price exceeds $300.

easy ecommerce

Write a query to return the department ID and headcount for every large department.

easy hr

Write a query to return the user ID and total session count for every user with at least one such session.

easy analytics

Write a query to return the user ID and average events per session for every highly engaged user.

medium analytics

Write a query to return the category ID and average price for every category whose average exceeds the cross-category average.

medium ecommerce

Write a query to return each qualifying customer's ID and their count of high-value orders.

medium ecommerce

Write a query to return each qualifying user's ID and their count of high-intensity sessions.

medium analytics

Write a query to return the count of top-engagement users and their average total event count as a single row.

hard analytics

Write a query to return the category ID and average price for every premium, well-stocked category.

hard ecommerce

Start learning to practice all 9 Chained CTEs problems, with instant grading and mastery tracking.

Common questions about Chained CTEs

Can two CTEs in one query share a name?

No. The statement is rejected, saying the name was specified more than once. Each CTE in the chain needs its own name, which is no hardship given that a descriptive name is what makes the chain readable in the first place.

Can the main query read a CTE other than the last one?

Yes. Every CTE in the chain is in scope for the final query, so you can select from an earlier step directly. That is what makes it possible to check an intermediate result without unpicking the rest of the query.

Can a CTE reference one that is defined after it?

No. Names become visible in the order they are written, so a CTE can only see the ones above it and a forward reference fails with the relation not existing. Order the chain the way the data flows and the restriction never comes up.