SQL Common Table Expressions

Understand the idea

WITH introduces the named result paid_totals. The final query joins those totals to customers so the output has readable names. Each CTE has a clear grain: one row per customer here. A CTE is a way to express a query; it is not a promise that the database materialises it or makes it faster.

Where to try it

Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.

SQLite sample data · MySQL / MariaDB sample data · See the sample tables

A worked example

WITH paid_totals AS (
  SELECT customer_id, SUM(total_cents) AS paid_cents
  FROM orders WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT c.name, p.paid_cents
FROM paid_totals AS p
JOIN customers AS c ON c.customer_id = p.customer_id
ORDER BY p.paid_cents DESC, c.customer_id;

Read the result

Mira has 2300 paid cents and Dev has 1350. Customers without paid orders do not appear because the final join is an inner join.

Try one change

Use a left join from customers to the CTE and COALESCE to display zero for customers without paid orders.