Lessons · SQL · naming a query with WITH
Naming a step
WITH name AS (query) gives a query a name for the rest of the statement. Nothing is stored; the name exists only inside that statement.
Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.
What it is for
Reports are built in steps: paid orders, then totals per customer, then the top ten. A CTE lets each step be read and tested on its own instead of nesting subqueries three deep.
How to think about it
Write the first step as a plain SELECT and check it. Wrap it in WITH step AS (...), write the next SELECT against step, check again. Chain with commas: WITH a AS (...), b AS (...).
Worked example
WITH paid AS (SELECT * FROM orders WHERE status = 'paid') SELECT customer_id, SUM(amount) AS total FROM paid GROUP BY customer_id;paid is the first step; the outer query reads it like a table.
WITH paid AS (SELECT * FROM orders WHERE status = 'paid'), per_customer AS (SELECT customer_id, SUM(amount) AS total FROM paid GROUP BY customer_id) SELECT * FROM per_customer ORDER BY total DESC LIMIT 10;Two named steps, then the top ten.
Your turn
Name the recent orders, then count them.
recent AS (SELECT * FROM orders WHERE placed >= '2026-01-01') SELECT COUNT(*) FROM recent;
Run a query against real tables
The trap
A CTE is not a saved table. Run the statement again and it is computed again; if you need it to persist, that is a view or a table.