CTE with WITH.

A CTE (Common Table Expression) is written WITH name AS (SELECT ...) followed by a main query that uses the name. It gives the same result as a subquery in FROM, but steps read top to bottom, like named intermediate results.

Important: a CTE is for structure and readability, not an automatic speedup. PostgreSQL decides how to execute the query; do not expect a CTE to make anything faster. These lessons use only plain (non-recursive) CTEs and read-only queries.

Use descriptive names (customer_totals, big_orders) and select only the columns you need.

Common mistakes: a comma before the main SELECT; omitting AS; trying to reuse a CTE in a later separate query (it lives for one statement); expecting a speedup.

Small example:

WITH big_orders AS (SELECT id, total FROM orders WHERE total >= 100) SELECT COUNT(*) AS big_count FROM big_orders;