CTEs: Building Queries Step by Step

By the end of this lesson you will be able to break a big business question into small, named steps with the WITH clause, check each step on its own, build a customer segmentation in three steps, and use a recursive CTE to find months with no orders.

Think about cooking biryani. You do not throw everything into one pot at once. You marinate the meat, fry the onions, half-cook the rice, then layer it all. Each step has a name and you can taste it before moving on. A CTE (common table expression) lets you write SQL the same way: one named step at a time.

What a CTE looks like

A CTE starts with WITH, gives a step a name, and puts a query in brackets. The main query after it can use that name like a table.

WITH step_name AS (
    SELECT ...
)
SELECT ...
FROM step_name;

You can chain several steps, separated by commas. Each step can use the steps before it. The CTE exists only while that one query runs; nothing is saved in the database.

Step 1: one row per order

Many business questions need order totals, but the shop stores items, not totals. So the first step is always the same: add up the items for each order.

WITH order_totals AS (
    SELECT o.order_id,
           o.customer_id,
           o.order_date,
           SUM(oi.quantity * oi.unit_price) AS order_value
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.order_id, o.customer_id, o.order_date
)
SELECT *
FROM order_totals
ORDER BY order_value DESC
LIMIT 5;

order_id | customer_id | order_date | order_value
---------+-------------+------------+------------
110      | 8           | 2025-04-01 | 32797
102      | 2           | 2025-01-12 | 15999
104      | 1           | 2025-02-02 | 8999
113      | 3           | 2025-05-02 | 8999
112      | 10          | 2025-04-18 | 8695
(5 rows)

What you should see: the five biggest orders, one row each. Now "one row means one order", so the double-counting trap from SQL Joins for Analysts cannot happen in the next steps.

Step 2: one row per customer

Next we summarise the orders per customer: how many orders, how much spent, and the average order value (AOV), which is total spent divided by number of orders.

WITH order_totals AS (
    SELECT o.order_id, o.customer_id,
           SUM(oi.quantity * oi.unit_price) AS order_value
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.order_id, o.customer_id
),
customer_summary AS (
    SELECT customer_id,
           COUNT(*) AS orders,
           SUM(order_value) AS total_spent,
           ROUND(SUM(order_value) * 1.0 / COUNT(*), 0) AS avg_order_value
    FROM order_totals
    GROUP BY customer_id
)
SELECT c.first_name, c.city, s.orders, s.total_spent, s.avg_order_value
FROM customer_summary AS s
JOIN customers AS c ON c.customer_id = s.customer_id
ORDER BY s.total_spent DESC;

first_name | city      | orders | total_spent | avg_order_value
-----------+-----------+--------+-------------+----------------
Sneha      | Delhi     | 1      | 32797       | 32797
Priya      | Jaipur    | 2      | 16899       | 8450
Aarav      | Rohtak    | 3      | 14944       | 4981
Rohan      | Mumbai    | 2      | 14397       | 7199
Isha       | Gurugram  | 1      | 8695        | 8695
Vikram     | Rohtak    | 1      | 4298        | 4298
Kabir      | Ludhiana  | 1      | 3948        | 3948
Farhan     | Lucknow   | 1      | 3798        | 3798
Meera      | Kochi     | 1      | 2499        | 2499
Arjun      | Hyderabad | 1      | 2199        | 2199
(10 rows)

What you should see: ten customers with their order count, total spend and AOV. Read the second CTE: it uses order_totals exactly like a table. This is the main power of CTEs: each step is small enough to read and check.

Step 3: segment the customers

Managers rarely want ten rows of numbers. They want segments: groups of similar customers they can treat differently. We add a third step that labels each customer, then count the segments.

WITH order_totals AS (
    SELECT o.order_id, o.customer_id,
           SUM(oi.quantity * oi.unit_price) AS order_value
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.order_id, o.customer_id
),
customer_summary AS (
    SELECT customer_id, COUNT(*) AS orders, SUM(order_value) AS total_spent
    FROM order_totals
    GROUP BY customer_id
),
segments AS (
    SELECT customer_id, total_spent,
           CASE
             WHEN orders >= 2 AND total_spent >= 10000 THEN 'Loyal high value'
             WHEN orders >= 2 THEN 'Loyal'
             WHEN total_spent >= 10000 THEN 'One-time big spender'
             ELSE 'One-time small'
           END AS segment
    FROM customer_summary
)
SELECT segment,
       COUNT(*) AS customers,
       SUM(total_spent) AS revenue
FROM segments
GROUP BY segment
ORDER BY revenue DESC;

segment              | customers | revenue
---------------------+-----------+--------
Loyal high value     | 3         | 46240
One-time big spender | 1         | 32797
One-time small       | 6         | 25437
(3 rows)

What you should see: three segments with customer counts and revenue. The plain "Loyal" segment is missing because, in this small shop, every repeat customer also spent ₹10,000 or more. A segment with no members simply does not appear in a GROUP BY result. Each segment suggests a different action: thank-you offers for loyal high-value customers, a "come back" offer for one-time big spenders, and so on. That is the moment analysis turns into a decision.

CTE or subquery?

The same logic can be written with subqueries nested inside each other. Both give the same result. Here is the customer count from Step 2 written as a subquery:

SELECT COUNT(*) AS customers_with_orders,
       ROUND(AVG(total_spent), 0) AS avg_spent_per_customer
FROM (
    SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.customer_id
) AS per_customer;

customers_with_orders | avg_spent_per_customer
----------------------+-----------------------
10                    | 10447
(1 row)

For one small step, a subquery is fine. For two or more steps, CTEs read from top to bottom like a recipe, while nested subqueries must be read from the inside out. Most analytics teams prefer CTEs for anything longer than a few lines.

Advanced: find missing months with a recursive CTE

A recursive CTE refers to itself, so it can generate rows, such as a list of months. Why would an analyst want that? Because GROUP BY only shows months that have data. A month with zero sales simply disappears, and a chart would hide the gap. Generating a full calendar and LEFT JOINing to it shows the zeros.

WITH RECURSIVE months AS (
    SELECT 1 AS m
    UNION ALL
    SELECT m + 1 FROM months WHERE m < 7
),
calendar AS (
    SELECT CONCAT('2025-0', m) AS month FROM months
)
SELECT cal.month,
       COUNT(o.order_id) AS orders
FROM calendar AS cal
LEFT JOIN orders AS o ON SUBSTR(o.order_date, 1, 7) = cal.month
GROUP BY cal.month
ORDER BY cal.month;

month   | orders
--------+-------
2025-01 | 3
2025-02 | 3
2025-03 | 3
2025-04 | 3
2025-05 | 3
2025-06 | 0
2025-07 | 0
(7 rows)

What you should see: seven months, with June and July showing 0 orders. Without the calendar, those months would not appear at all. Here they mean the practice data ends in May. In a real business, a zero month would be a loud alarm.

Common mistakes

• Forgetting the comma between CTEs. Write WITH a AS (...), b AS (...), and use WITH only once.
• A semicolon inside the chain. The whole WITH ... SELECT is one statement; only the end gets ;.
• Expecting a CTE to be saved. It vanishes after the query. Use a view if you need to reuse it.
• Mixing levels in one step. Keep each CTE at one level: one row per order, then one row per customer.

Key points

• A CTE names a step with WITH name AS (...); the main query uses it like a table.
• Chain CTEs to build a question step by step, each readable on its own.
• A common pattern is items, then orders, then customers, then segments.
• Debug by selecting from any single step.
• Recursive CTEs generate rows such as calendars, so missing periods show as zero.

WITH order_totals AS (
    SELECT o.order_id, o.customer_id,
           SUM(oi.quantity * oi.unit_price) AS order_value
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.order_id, o.customer_id
),
with_city AS (
    SELECT t.order_id, t.order_value, c.city
    FROM order_totals AS t
    JOIN customers AS c ON c.customer_id = t.customer_id
)
SELECT city,
       COUNT(*) AS orders,
       ROUND(AVG(order_value), 0) AS avg_order_value
FROM with_city
GROUP BY city
ORDER BY avg_order_value DESC;