CTEs: Break a Long Query Into Named Steps

Long queries go wrong in a boring way. They get nested one level at a time, each level makes sense when you add it, and a month later nobody can say what the middle one returns. The fix is not a cleverer query. It is giving each step a name, so you can read it, run it alone and check it.

A CTE (common table expression) is that name. You write WITH name AS (query) and then use name like a table in the rest of the statement. It is standard SQL.

The nested version

Take a question with two steps of aggregation. Which customers spent more than the average customer, ignoring cancelled orders? First you total each order, then each customer, then compare with the average of the customer totals. Written inline:

SELECT c.name, ct.orders, ct.spent
FROM customers AS c
JOIN (
  SELECT o.customer_id, count(*) AS orders, sum(t.total) AS spent
  FROM orders AS o
  JOIN (
    SELECT i.order_id, sum(i.quantity * p.price) AS total
    FROM order_items AS i
    JOIN products AS p ON p.product_id = i.product_id
    GROUP BY i.order_id
  ) AS t ON t.order_id = o.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.customer_id
) AS ct ON ct.customer_id = c.customer_id
WHERE ct.spent > (
  SELECT avg(spent) FROM (
    SELECT sum(t2.total) AS spent
    FROM orders AS o2
    JOIN (
      SELECT i2.order_id, sum(i2.quantity * p2.price) AS total
      FROM order_items AS i2
      JOIN products AS p2 ON p2.product_id = i2.product_id
      GROUP BY i2.order_id
    ) AS t2 ON t2.order_id = o2.order_id
    WHERE o2.status <> 'cancelled'
    GROUP BY o2.customer_id
  ) AS x
)
ORDER BY ct.spent DESC;

   name    | orders | spent
-----------+--------+-------
 Ben Cruz  |      2 | 63.55
 Ana Reyes |      3 | 55.75
 Hugo Sy   |      1 | 45.75
(3 rows)

It is correct, and it is hard to trust. The order totals are written twice, and the average needs the whole customer-total query again.

The same query in named steps

WITH order_totals AS (
  SELECT i.order_id, sum(i.quantity * p.price) AS total
  FROM order_items AS i
  JOIN products AS p ON p.product_id = i.product_id
  GROUP BY i.order_id
),
customer_totals AS (
  SELECT o.customer_id, count(*) AS orders, sum(t.total) AS spent
  FROM orders AS o
  JOIN order_totals AS t ON t.order_id = o.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.customer_id
)
SELECT c.name, ct.orders, ct.spent
FROM customer_totals AS ct
JOIN customers AS c ON c.customer_id = ct.customer_id
WHERE ct.spent > (SELECT avg(spent) FROM customer_totals)
ORDER BY ct.spent DESC;

   name    | orders | spent
-----------+--------+-------
 Ben Cruz  |      2 | 63.55
 Ana Reyes |      3 | 55.75
 Hugo Sy   |      1 | 45.75
(3 rows)

Same rows, same order. Read it from the top: order totals, then customer totals, then the answer. Each step is written once, and customer_totals is used twice, in the join and in the average. Separate CTEs are separated by a comma, and only the first one has the word WITH.

Test one step at a time

The best part is that a step is a query you can run. To check the order totals, keep the WITH part and replace the last SELECT.

WITH order_totals AS (
  SELECT i.order_id, sum(i.quantity * p.price) AS total
  FROM order_items AS i
  JOIN products AS p ON p.product_id = i.product_id
  GROUP BY i.order_id
)
SELECT * FROM order_totals ORDER BY order_id LIMIT 3;

 order_id | total
----------+-------
        1 | 13.25
        2 | 18.00
        3 | 33.65
(3 rows)

Check one against a number you can work out by hand. Order 1 is two notebooks at 3.50 and five gel pens at 1.25, which is 13.25. If a step is wrong, you find it there, not in the middle of 30 lines.

When a CTE runs once and when it is inlined

This part is about PostgreSQL, not the standard. Since PostgreSQL 12, a CTE that is used once and has no side effects is folded into the main query, as if you had written it inline. A CTE used more than once is computed once and kept. You can ask for either on purpose.

WITH customer_totals AS MATERIALIZED (...)      -- compute once, keep the result
WITH customer_totals AS NOT MATERIALIZED (...)  -- fold into the query

Why would you care? If a CTE is expensive and you use it twice, keeping it is good. If it is cheap and you filter it afterwards, folding lets the planner use the filter early. The default is usually right. Look at the plan before you force anything. You can see the difference with EXPLAIN, which the last section covers.

A CTE that changes data

In PostgreSQL a CTE can also be an INSERT, UPDATE or DELETE with RETURNING, and the next step can use the rows it returned. This is PostgreSQL-specific. Insert a customer and log the signup in one statement.

CREATE TABLE signup_log (customer_id integer, logged_on date);

WITH new_customer AS (
  INSERT INTO customers (name, email, city, joined)
  VALUES ('Iris Lao', 'iris@example.com', 'Manila', '2026-10-01')
  RETURNING customer_id, joined
)
INSERT INTO signup_log SELECT customer_id, joined FROM new_customer;

SELECT * FROM signup_log;

CREATE TABLE
INSERT 0 1
 customer_id | logged_on
-------------+------------
           9 | 2026-10-01
(1 row)

Both writes happen in one statement, so either both happen or neither does.

Common mistakes

• Putting a second WITH in front of a second CTE instead of a comma.
• Writing a CTE that is never used. PostgreSQL does not complain.
• Treating a CTE as a guarantee of speed or of order.
• Repeating the same subquery in several places when one named step would do.
• Naming steps a, b and c. The names are the documentation.

If you skip this, the next long query you write will be a nest nobody can review, and one wrong step inside it will look like a wrong answer. When you last read your own long query six months later, how long did it take to trust it?