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;

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.

Practise naming a query with WITH on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.