What is a CTE (WITH clause) and why would you use one?
A common table expression names a subquery at the top of a statement so you can read it in steps and reuse it more than once. It improves readability; since PostgreSQL 12 a simple CTE is usually inlined like a subquery.
Explain it simply
A long recipe is easier when written as "first make the masala, then use the masala in the curry". A CTE is a named intermediate step. Instead of nesting subqueries inside subqueries, you list the steps in order and give each a name.
Example
WITH big_orders AS (
SELECT customer_id, sum(total) AS spent
FROM orders
WHERE created_at >= date_trunc('month', now())
GROUP BY customer_id
HAVING sum(total) > 10000
)
SELECT c.name, b.spent
FROM big_orders b
JOIN customers c ON c.id = b.customer_id
ORDER BY b.spent DESC;You can chain several CTEs, and later ones can use earlier ones:
WITH a AS (SELECT ...),
b AS (SELECT ... FROM a)
SELECT * FROM b;A CTE exists only for that one statement. It is not a temporary table and has no index of its own.
What to say in the interview
"A CTE gives a subquery a name so the query reads top-to-bottom. It lives for one statement. In PostgreSQL 12 and later, a non-recursive CTE referenced once is inlined, so it normally has no performance penalty versus a subquery."
Common mistakes
- Thinking a CTE stores results for later statements.