Building Chained CTE Pipelines

Welcome back to Structuring Queries with Common Table Expressions. We have completed the first lesson and are now beginning Unit 2 of 4. Previously, we gave one intermediate result a clear name with order_totals. Now, we will extend that idea by building a single MySQL statement with multiple CTE stages.

We will follow the data from line-item value, to one total per order, to enriched records containing customer and date information, and finally to a deterministic chronological report.

As you may recall from the previous lesson, order_totals calculates each order’s total value by joining OrderItems to Products. We will keep this calculation unchanged because it already performs one focused job correctly. The stage receives detailed line items and returns one rounded total for each order.

WITH order_totals AS (
    SELECT
        oi.order_id,
        ROUND(SUM(oi.quantity * p.product_price), 2) AS order_total
    FROM OrderItems AS oi
    INNER JOIN Products AS p
        ON p.product_id = oi.product_id
    GROUP BY oi.order_id
)

The important idea is the CTE’s contract: each order_id appears once, alongside its calculated order_total. The alias oi represents order items, while p represents products. Multiplying quantity by product price creates line-item value, SUM adds those values per order, and ROUND keeps the result at two decimal places.

The next stage enriches the prepared totals with information from Orders. We add another CTE after the first one, placing a comma between the two definitions. The second CTE can read from order_totals because that name has already been declared.

,
customer_orders AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.order_date,
        ot.order_total
    FROM Orders AS o
    INNER JOIN order_totals AS ot
        ON ot.order_id = o.order_id
)

Here, o identifies the order source, and ot identifies the earlier CTE. The shared order_id connects each order record to its prepared total. The result exposes four useful fields: the order identifier, customer identifier, order date, and rounded total.

Dependency order matters in a chained CTE pipeline. A later CTE may consume an earlier CTE, but the earlier stage must appear first so MySQL knows what order_totals represents when it reads customer_orders.

Once customer_orders contains all the fields needed for the report, the main query can stay simple. It reads prepared columns instead of repeating the order-total calculation or the join between Orders and order_totals.

SELECT
    order_id,
    customer_id,
    order_date,
    order_total
FROM customer_orders
ORDER BY order_date, order_id

This final SELECT acts as the presentation layer. It chooses which columns appear and how rows are arranged. The report is sorted by order_date, so older orders appear first. If several orders share a date, order_id resolves the tie. Because order_id uniquely identifies an order, this produces a total, deterministic order every time.

We can now combine the stages into one MySQL statement. Notice how each part has one responsibility: calculation, enrichment, or presentation.

WITH order_totals AS (
    SELECT
        oi.order_id,
        ROUND(SUM(oi.quantity * p.product_price), 2) AS order_total
    FROM OrderItems AS oi
    INNER JOIN Products AS p
        ON p.product_id = oi.product_id
    GROUP BY oi.order_id
),
customer_orders AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.order_date,
        ot.order_total
    FROM Orders AS o
    INNER JOIN order_totals AS ot
        ON ot.order_id = o.order_id
)
SELECT
    order_id,
    customer_id,
    order_date,
    order_total
FROM customer_orders
ORDER BY order_date, order_id;

We can trace the rows through the pipeline in three steps:

1. order_totals changes many order-item rows into one rounded order-total row per order.
2. customer_orders joins those totals to Orders, adding the customer and date.
3. The final SELECT presents the four prepared fields in chronological order.

The pipeline’s dependencies and changing row shape can be visualized from left to right:

For the seeded data, the statement returns one report row for each of the 600 orders. The main query does not need to know how each order total was calculated, which keeps the complete statement easier to read and maintain.

A chained CTE pipeline lets a later named stage consume the result of an earlier one. In this lesson, order_totals isolated the order-total calculation, while customer_orders added customer and date information. The final SELECT then presented the prepared fields and used order_date, order_id for deterministic chronological ordering.

The key pattern is to declare CTEs in dependency order and give each stage a focused responsibility. In the upcoming practices, we will first inspect the starting query and source relationships, then build the two-stage pipeline, and finally complete the full report. Let’s use those steps to turn this structure into reliable SQL that we can build independently.