Tier 5 · Expert

Multi-CTE Query Architecture in SQL

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

Before this Chained CTEs, Joining Multiple Tables, Aggregate Window Functions (SUM, AVG, COUNT OVER), Conditional Aggregation (CASE inside Aggregates)

Builds toward Choosing Between Subqueries, CTEs, and Joins, Analyst Debugging Patterns

What are Multi-CTE Queries in SQL?

Multi-CTE query architecture is about how you structure a complex analytical query so that other people can read it, debug it, and change one part without breaking everything else.

You already know how to chain CTEs. This node is about when and how to split a query into layers deliberately — not just to avoid repetition, but to make the logic visible.

How many CTEs should a SQL query have?

The core principle: one transformation per CTE. Each CTE should do exactly one thing — join, filter, aggregate, apply window functions, or reshape. When a CTE joins and aggregates in the same step, neither operation can be verified independently. If the result is wrong, you don't know which step failed.

Here's the pattern — a query that joins orders to customers, then ranks by order value:

WITH order_customers AS (
  SELECT o.id AS order_id, o.total_amount, c.name AS customer_name
  FROM orders o JOIN customers c ON o.customer_id = c.id
),
top_orders AS (
  SELECT *, RANK() OVER (ORDER BY total_amount DESC) AS rnk
  FROM order_customers
)
SELECT customer_name, total_amount FROM top_orders WHERE rnk <= 5

order_customers handles the join. top_orders applies the ranking. The final SELECT filters and orders. Each CTE does one thing and each step is independently checkable — you can run any CTE in isolation to verify it before reading the next.

How should you name a CTE?

Naming matters

A CTE named cte1 or temp tells the reader nothing. monthly_totals tells them exactly what the step produces and why it exists. Descriptive names function as inline documentation. They make it possible to spot which layer produced a wrong result just by reading the chain.

What order should CTEs be written in?

Order CTEs in logical flow

Write CTEs in the order they depend on each other — source data first, transformations next, final shaping last. PostgreSQL doesn't require this, but a query that reads top-to-bottom without jumping around to understand which step feeds which is a query that can be reviewed and modified by someone who didn't write it. Logical ordering is documentation.

Why should two CTEs never recompute the same value?

The one thing that trips people up

Computing the same expression in two different CTEs. If a date truncation or column derivation appears in both monthly_totals and ranked_users with slightly different wording, you've created a silent inconsistency. Compute it once in the earliest CTE that needs it and pass it forward. The rule: if two CTEs reference the same derived value, one of them should be reading it from the other, not recomputing it.

Practice Multi-CTE Queries in SQL

Practice · easy ecommerce · Brightlane

Scenario: Brightlane's CRM team is building a customer overview report that lists each customer alongside how many orders they have placed and how much they have spent.

Task: Write a query to return each customer's id, name, number of orders placed, and total amount spent across all their orders.

Assumptions:

The result covers only customers who have placed at least one order.

Output:

One row per customer who has placed at least one order.
Columns in this order: customer_id, customer_name, order_count, total_spent.
Sorted by total_spent descending.

Schema · ecommerce5 tables? = nullable

10 Multi-CTE Queries practice problems

Write a query to return each customer's id, name, number of orders placed, and total amount spent across all their orders.

easy ecommerce

Write a query to return each department name, the number of active employees in it, and the average current salary across those employees.

easy hr

Write a query to return each product's id, name, and total revenue earned from its line items in order_items.

easy ecommerce

Write a query to return the name, total revenue, and total units sold for each product category that has generated more than $500 in revenue across its line items.

medium ecommerce

Write a query to return each department name, the number of active employees in it, the average current salary across those employees, and the total current payroll, restricted to departments where the average current salary exceeds $130,000.

medium hr

Write a query to return every customer who has placed at least one order, with their id, name, total revenue from delivered orders, total revenue from cancelled orders, and total order count.

medium ecommerce

Write a query to return each plan, the number of active users on that plan, the total events those users have generated across all time, and the average events per active user.

medium analytics

Write a query to return every product that has appeared on at least one order line, with its id, name, the number of line items it appeared on, the total revenue it generated, and its share of total revenue across all products.

hard ecommerce

Write a query to return each active employee with a current salary on record, with their name, department name, current salary, the average current salary across all active employees in their department, and the difference between their salary and that department average.

hard hr

Write a query to return every user whose total conversion revenue exceeds the average total conversion revenue across all converting users on the same plan, with their id, plan, country, and total conversion revenue.

hard analytics

Start learning to practice all 10 Multi-CTE Queries problems, with instant grading and mastery tracking.

Common questions about Multi-CTE Queries

How do you decide where one CTE should end and the next begin?

At the point where you could describe what you have in a short phrase. If the name you want to give a step needs the word and in it, it is doing two things and wants splitting, because a step you cannot name is a step you cannot check.

Is it worth naming a CTE you only use once?

Often yes. The name is the documentation, and a reader who can see that a step produces monthly totals does not have to work it out from the SQL. The cost is one line; the benefit lands on everyone who reads the query afterwards.

What is the sign that a query has too many CTEs?

When you cannot say what a step produces without reading its body, or when two steps compute the same thing in slightly different words. Both mean the chain has stopped being a sequence of ideas and become a place to put SQL.