Chaining CTEs for Complex Data Transformations

From One CTE to a Chain

In the real world, many data questions can't be answered in a single step. You often need to:

1. Filter data to an initial set.
2. Aggregate or enrich that set.
3. Perform a final calculation on the aggregated results.

Chaining CTEs allows your SQL code to mirror this logical, step-by-step process. Instead of creating a messy "subquery within a subquery," you define a sequence of named, temporary tables.

The syntax is straightforward. You use the WITH keyword only once, and then separate each subsequent CTE definition with a comma.

WITH
  cte_1 AS (
    -- First logical step
    SELECT ... FROM some_table
  ), -- Note the comma
  cte_2 AS (
    -- Second logical step, which can use the result of cte_1
    SELECT ... FROM cte_1
  )
-- Final query that can use either cte_1 or cte_2
SELECT ... FROM cte_2;

Building a Multi-Step Query

Let's see this in action. A common task is to analyze data from different perspectives and then combine those perspectives. For example, we might want to find all "chicken" items on a menu and also find the most expensive item in each menu category, and then see how those two sets of information relate.

Watch this video to see how two CTEs can be defined and then joined together in the final query.

Chaining CTEs Sequentially

The previous example showed two largely independent CTEs. The real power of chaining comes when one CTE directly processes the output of the one before it. This creates a true logical sequence.

A classic example of this is calculating an "average of averages"—for instance, finding the average salary for each department, and then calculating the average of those departmental averages. A simple AVG(salary) across the whole company won't give you the right answer if departments are of different sizes. You have to do it in two steps.

This next video provides a perfect walkthrough of this exact problem.

WITH vip_customers AS (
    -- Step 1: Find customer IDs who have spent more than $1000
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING SUM(order_amount) > 1000
),
vip_orders AS (
    -- Step 2: Get all orders placed by those VIP customers
    SELECT o.order_amount
    FROM orders AS o
    JOIN vip_customers AS v
      ON o.customer_id = v.customer_id
)
-- Final Step: Calculate the average order amount from the VIP orders
SELECT AVG(order_amount) AS avg_vip_order_amount
FROM vip_orders;

A Practical Data Pipeline Example

Let's look at one more example that resembles a real-world task you might get in a technical support or junior developer role: analyzing log data or user activity. Here, we'll see how a series of CTEs can be used to take raw data, parse it, create multiple aggregations, and then join those aggregations for a final report.

This article provides a step-by-step construction of a query with four chained CTEs.

For a final, concise set of examples, you can also look over this next article. It reinforces the patterns we've discussed today with practical scenarios.

Conclusion

You've now learned how to connect CTEs into a logical chain, a technique that dramatically expands the complexity of problems you can solve with clean, readable SQL. By breaking down a large problem into a sequence of smaller, verifiable steps, you make your code easier to write, debug, and share with your team.

Key Takeaways:

• You can define multiple CTEs in a single query by using the WITH keyword once and separating each CTE with a comma.
• CTEs can be "chained" by having a later CTE query the results of a preceding one.
• This approach allows you to build multi-step data transformation pipelines within a single SQL statement, making your logic explicit and easy to follow from top to bottom.
• This is a fundamental technique for writing professional, production-quality SQL.

Preview of the Next Lesson:
Now that you know how to build queries with both single and chained CTEs, our next lesson will be a practical workshop. We'll focus on the essential skill of refactoring: taking existing, messy queries that use nested subqueries (the kind you might find in a legacy codebase) and transforming them into clean, maintainable CTE-based structures. This is a critical, job-ready skill that demonstrates your ability to not only write good code but also to improve existing code.