SQL Common Table Expressions: Make a Long Query Easier to Read

A common table expression, usually called a CTE, gives a temporary name to a query result inside one statement. It is useful when a report becomes easier to understand as a sequence of named steps. You do not need to create a permanent table just to give a calculation a clear label.

Practice setup: Use the fictional database in the SQL practice lab. Run that setup once in a fresh SQLite practice database. Customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional units. The examples in this series use SQLite syntax; check your database's documentation before adapting them elsewhere.

Start with the intermediate question

We want customers whose order total is at least 1500. First calculate one row per ordering customer. Name that result customer_totals so the next step can read it like a small input table.

WITH customer_totals AS (
  SELECT customer_id,
         COUNT(*) AS order_count,
         SUM(amount) AS total_amount
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, order_count, total_amount
FROM customer_totals
WHERE total_amount >= 1500
ORDER BY customer_id;

The result is customer 1 with 2 orders and 1800, followed by customer 3 with 1 order and 1500. WITH introduces the CTE. The parentheses contain its query. The final SELECT uses the named result.

Understand the lifetime of the name

customer_totals exists only for this statement. A separate SELECT * FROM customer_totals; will not find a saved table unless you independently created one with that name. Run the WITH clause and final SELECT together.

The name is a readability tool, not a guarantee that the database stores or calculates the result once in a particular way. Query planning is the database's job. Choose a CTE to clarify the logic, then measure performance for a real workload rather than assuming the name makes it faster.

Add customer names in a second step

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, t.total_amount
FROM customer_totals AS t
JOIN customers AS c ON c.customer_id = t.customer_id
ORDER BY t.total_amount DESC, c.customer_id;

The result is Amina 1800, Chao 1500 and Brian 800. Aggregating orders first makes the intended result shape visible: one total per customer, then a name lookup. The customer primary key prevents a matching name from multiplying each total.

Debug one step at a time

Temporarily replace the final SELECT with SELECT * FROM customer_totals ORDER BY customer_id; to inspect the intermediate result. Check that its three totals sum to 4100 before adding another transformation. This is often easier than staring at a large query containing several nested calculations.

Use descriptive names such as customer_totals rather than data1 and data2. Avoid reusing a real table's name, which can make it harder to tell which input a query refers to. Keep each CTE focused on one logical step.

Try it yourself

Use a CTE to calculate order counts and average amounts per customer. Return only customers with at least two orders. Show customer_id, order_count and average_amount.

WITH customer_summary AS (
  SELECT customer_id, COUNT(*) AS order_count,
         AVG(amount) AS average_amount
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, order_count, average_amount
FROM customer_summary
WHERE order_count >= 2
ORDER BY customer_id;

Keep exploring: SQL learning path.