MySQL Multiple CTEs: Chain Steps in One Query

A single WITH can define several CTEs, separated by commas. Each one can use the CTEs defined before it. This turns a complicated report into a clear pipeline.

query.sql

SQL

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    ordered_on DATE NOT NULL,
    total DECIMAL(8,2) NOT NULL,
    status VARCHAR(10) NOT NULL
);
INSERT INTO orders (customer_id, ordered_on, total, status) VALUES
    (1, '2026-04-02', 100, 'paid'), (1, '2026-05-03', 150, 'paid'), (1, '2026-05-20', 60, 'cancelled'),
    (2, '2026-04-15', 300, 'paid'), (2, '2026-06-02', 80, 'paid'),
    (3, '2026-05-10', 500, 'paid'), (3, '2026-05-11', 40, 'refunded'),
    (4, '2026-06-20', 90, 'paid');

A three-step report

Question: “For each month, how much did we sell, how much was lost to cancellations and refunds, and what is the loss as a percentage?”

query.sql

SQL

WITH monthly AS (
    SELECT DATE_FORMAT(ordered_on, '%Y-%m') AS month, status, SUM(total) AS amount
    FROM orders
    GROUP BY DATE_FORMAT(ordered_on, '%Y-%m'), status
),
pivoted AS (
    SELECT month,
           SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS sold,
           SUM(CASE WHEN status IN ('cancelled', 'refunded') THEN amount ELSE 0 END) AS lost
    FROM monthly
    GROUP BY month
),
with_percent AS (
    SELECT month, sold, lost, ROUND(100 * lost / NULLIF(sold + lost, 0), 1) AS lost_percent
    FROM pivoted
)
SELECT month, sold, lost, lost_percent
FROM with_percent
ORDER BY month;

Output

OUTPUT

+---------+--------+--------+--------------+
| month   | sold   | lost   | lost_percent |
+---------+--------+--------+--------------+
| 2026-04 | 400.00 |   0.00 |          0.0 |
| 2026-05 | 650.00 | 100.00 |         13.3 |
| 2026-06 | 170.00 |   0.00 |          0.0 |
+---------+--------+--------+--------------+
3 rows in set

Read it like a recipe:

monthly: total amount per month and status.
pivoted: turn the statuses into two columns, sold and lost.
with_percent: add the percentage.
The final SELECT picks and sorts.

Each step has one job and a name. To debug it, replace the last SELECT with SELECT * FROM pivoted and look at the intermediate result. That is much easier than with nested subqueries.

Debugging step by step

query.sql

SQL

WITH monthly AS (
    SELECT DATE_FORMAT(ordered_on, '%Y-%m') AS month, status, SUM(total) AS amount
    FROM orders
    GROUP BY DATE_FORMAT(ordered_on, '%Y-%m'), status
)
SELECT * FROM monthly ORDER BY month, status;

Output

OUTPUT

+---------+-----------+--------+
| month   | status    | amount |
+---------+-----------+--------+
| 2026-04 | paid      | 400.00 |
| 2026-05 | cancelled |  60.00 |
| 2026-05 | paid      | 650.00 |
| 2026-05 | refunded  |  40.00 |
| 2026-06 | paid      | 170.00 |
+---------+-----------+--------+
5 rows in set

A funnel: customers by how far they got

query.sql

SQL

WITH first_order AS (
    SELECT customer_id, MIN(ordered_on) AS first_on FROM orders GROUP BY customer_id
),
paid_orders AS (
    SELECT customer_id, COUNT(*) AS paid_count, SUM(total) AS paid_total FROM orders WHERE status = 'paid' GROUP BY customer_id
),
summary AS (
    SELECT f.customer_id, f.first_on, COALESCE(p.paid_count, 0) AS paid_count, COALESCE(p.paid_total, 0) AS paid_total
    FROM first_order f
    LEFT JOIN paid_orders p ON p.customer_id = f.customer_id
)
SELECT customer_id, first_on, paid_count, paid_total,
       CASE WHEN paid_count >= 2 THEN 'repeat' WHEN paid_count = 1 THEN 'one-time' ELSE 'never paid' END AS segment
FROM summary
ORDER BY customer_id;

Output

OUTPUT

+-------------+------------+------------+------------+----------+
| customer_id | first_on   | paid_count | paid_total | segment  |
+-------------+------------+------------+------------+----------+
|           1 | 2026-04-02 |          2 |     250.00 | repeat   |
|           2 | 2026-04-15 |          2 |     380.00 | repeat   |
|           3 | 2026-05-10 |          1 |     500.00 | one-time |
|           4 | 2026-06-20 |          1 |      90.00 | one-time |
+-------------+------------+------------+------------+----------+
4 rows in set

Each CTE is a small, named question: “when was the first order?”, “how many paid orders?”, “combine them”. The final query adds the label.

Rules for several CTEs

Separate them with commas. Write WITH only once.
A CTE can use earlier CTEs, but not later ones (except in recursive CTEs, which can use themselves).
Give each CTE a clear name.
If a CTE is used by the next one only, that is fine. If it is used in several places, the reuse is a bonus.

Mixing recursive and normal CTEs

If any CTE in the list is recursive, write WITH RECURSIVE once at the start. The non-recursive CTEs may be in the same list:

query.sql

SQL

WITH RECURSIVE numbers AS (
    SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n < 5
),
squares AS (
    SELECT n, n * n AS square FROM numbers
)
SELECT SUM(square) AS sum_of_squares FROM squares;

Output

OUTPUT

+----------------+
| sum_of_squares |
+----------------+
|             55 |
+----------------+
1 row in set

Is it faster?

A CTE is mostly about readability. MySQL may merge a CTE into the main query (like a view) or materialise it into a temporary table, depending on the query. If the same CTE is used twice, MySQL materialises it once and reuses it. Do not expect speed from a CTE by itself. If a report is slow, check EXPLAIN and the indexes (CTE vs Subquery vs View).

lightbulb

Write the pipeline first

Sketch the steps in plain words (1. monthly totals, 2. split into sold and lost, 3. add percent). Then each step becomes one CTE.