Using Multiple CTEs in a Single Query

by

Common Table Expressions (CTEs) are a handy way to break down a complex query into readable, reusable pieces. When you need several intermediate results – say, a filtered set, an aggregation, and a ranking – you can stack multiple CTE definitions together. PostgreSQL, SQL Server, MySQL 8+, and many other engines support this syntax.

Why Stack CTEs?

There are often multiple ways of achieving the same outcome in SQL, and CTEs are no exception. You might find that you can get the result without using multiple CTEs. But there are benefits to be had in stacking CTEs:

  • Readability: Each logical step gets its own named block, so anyone glancing at the query can see the workflow.
  • Reusability: Later parts of the query can reference any earlier CTE without repeating subqueries.
  • Modularity: You can comment or tweak individual CTEs without touching the rest of the statement.

Anatomy of a Multi‑CTE Query

Using multiple CTEs in a single query looks something like this:

WITH
    cte_name_1 AS ( … ),
    cte_name_2 AS ( … ),
    cte_name_3 AS ( … )
SELECT …
FROM   final_cte;

The WITH clause introduces a comma‑separated list of CTEs. They are evaluated sequentially, meaning each CTE can depend on any that appear before it.

Example

Imagine a small online bookstore. We want to:

  1. Pull all orders placed in the last 30 days.
  2. Calculate the total revenue per customer from those orders.
  3. Rank customers by revenue, showing only the top three.

Below is a multi‑CTE query that accomplishes this without resorting to temporary tables or nested subqueries:

WITH
    recent_orders AS (
        SELECT
            o.order_id,
            o.customer_id,
            o.order_date,
            oi.quantity,
            oi.unit_price
        FROM   dbo.orders      AS o
        JOIN   dbo.order_items AS oi ON o.order_id = oi.order_id
        WHERE  o.order_date >= DATEADD(day, -30, CAST(GETDATE() AS DATE))
    ),

    revenue_per_customer AS (
        SELECT
            customer_id,
            SUM(quantity * unit_price) AS total_spent
        FROM   recent_orders
        GROUP BY customer_id
    ),

    ranked_customers AS (
        SELECT
            customer_id,
            total_spent,
            ROW_NUMBER() OVER (ORDER BY total_spent DESC) AS rank
        FROM   revenue_per_customer
    )
SELECT
    rc.rank,
    c.name              AS customer_name,
    rc.total_spent
FROM   ranked_customers AS rc
JOIN   dbo.customers    AS c ON rc.customer_id = c.id
WHERE  rc.rank <= 3
ORDER BY rc.rank;

This query is built for SQL Server. If you use a different DBMS, you may need to adjust a few things, depending on the RDBMS and your query. In any case, the main point of the example is to demonstrate how multiple CTEs can be used for a single query.

Here’s a quick walk‑through of the above query:

  1. The recent_orders CTE filters the raw orders and order_items tables to the last month. This isolates the timeframe we care about and keeps later calculations lightweight.
  2. The revenue_per_customer CTE aggregates the filtered rows, turning line‑item quantities and prices into a single total_spent figure per customer. Because it reads from recent_orders, it automatically inherits the date filter.
  3. The ranked_customers CTE applies a window function (ROW_NUMBER()) to assign a rank based on descending revenue. The ranking logic lives in its own CTE, making the final SELECT straightforward.
  4. The final SELECT statement joins the ranked result back to the customers table for human‑readable names, then limits the output to the top three ranks.

Things to Keep in Mind

  • Order matters: A CTE can only reference those defined earlier. Trying to use revenue_per_customer inside recent_orders would raise an error.
  • Performance: Some modern engines materialize CTEs lazily, treating them like inline views. However, if a CTE is referenced multiple times, the optimizer may choose to compute it once and reuse the result, which can be a win for heavy calculations.
  • Recursion: Some databases allow recursive CTEs (WITH RECURSIVE). Those are a special case and must be the first CTE in the list.

In summary, stacking CTEs lets you decompose a query into logical steps. By naming each step, you gain clarity and flexibility without sacrificing performance.