Common Table Expressions (WITH)

After this lesson you can write a multi-step query as named steps read top to bottom, reuse one step twice, and put WITH in front of data changes. In the last lesson you nested a SELECT inside FROM as a derived table; a CTE (common table expression) is that same query given a name and moved to the top.

Naming a step

A WITH clause defines one or more named queries that exist for the one statement that follows. Take a table of parcels per delivery, whose totals per depot are north 42, south 10, east 25 and west 9:

CREATE TABLE deliveries (depot TEXT, parcels INTEGER);
-- rows: north 12, south 7, north 30, east 20, south 3, east 5, west 9

WITH per_depot(depot, total) AS (
    SELECT depot, SUM(parcels) FROM deliveries GROUP BY depot
)
SELECT depot, total FROM per_depot WHERE total >= 20 ORDER BY depot;

east|25
north|42

per_depot(depot, total) is a column list: it names the columns of the CTE, so the inner SUM(parcels) needs no alias. Without the list, the names come from the inner SELECT.

Chaining, and reading one step twice

A CTE may read the ones written above it, and the main query may read the same CTE as often as it likes. Here per_depot is read twice inside shares (once for the rows, once for the grand total):

WITH per_depot(depot, total) AS (
    SELECT depot, SUM(parcels) FROM deliveries GROUP BY depot
),
shares AS (
    SELECT depot, total * 100 / (SELECT SUM(total) FROM per_depot) AS pct
    FROM per_depot
)
SELECT depot, pct FROM shares WHERE pct > 15 ORDER BY pct DESC;

north|48
east|29

With derived tables you would write the GROUP BY query out twice. Note pct is integer division (4200 / 86 = 48). A comma separates CTEs; there is no comma before the main SELECT.

A VALUES list as a mini-table

VALUES can be a CTE body, which gives you an inline lookup table with no CREATE:

WITH rates(code, pct) AS (VALUES ('A', 10), ('B', 5))
SELECT i.item, i.price * r.pct / 100 AS discount
FROM items i LEFT JOIN rates r ON r.code = i.code
ORDER BY i.item;

desk|10
lamp|4
mug|5
rug|

rug has code C, which is not in rates, so the LEFT JOIN keeps it with a NULL (printed as nothing).

WITH before INSERT, UPDATE and DELETE

The WITH goes in front of the data-changing statement and is visible only to it:

WITH stale AS (SELECT id FROM sessions WHERE last_seen < 10)
DELETE FROM sessions WHERE id IN (SELECT id FROM stale);

Run it on four sessions and SELECT id, user FROM sessions prints 2|bo and 4|di. A later statement that mentions stale fails with no such table: stale: a CTE is gone when its statement ends, unlike a view.

Traps

A CTE hides a real table of the same name. Inside that statement, WITH sessions AS (SELECT 1 AS id) SELECT COUNT(*) FROM sessions; prints 1, although the table holds 4 rows. No error warns you.

Do not tune it. SQLite expands a CTE used once into the query, and computes one used twice a single time and keeps the result. Newer engines let you force either with AS MATERIALIZED; SQLite 3.27, the version here, rejects that keyword. Recursive CTEs (WITH RECURSIVE) come in the advanced course.

Common mistakes

• Using JOIN instead of LEFT JOIN for a per-row total: rows with no match vanish, and any average taken over the totals comes out too high.
• Forgetting COALESCE on a SUM over zero rows: it is NULL, and AVG silently skips NULLs, which shifts the average the same way.
• Grouping by a name when names can repeat: two different people with one name become one row with a doubled total. Group by the id.
• Writing >= when the task says "above": a row exactly equal to the average slips into the result.

Your exercise

Using the customers and orders in the starter, work out each customer's total spend, then the average of those totals, and list the customers strictly above that average with how far above they are. Customers with no orders count as 0, and two customers may share a name, so the steps need to be named CTEs, and the average is of the totals, not of the order amounts.