CTE (Common Table Expression)
A named, temporary result set defined with WITH that exists only for the duration of one query. Makes complex queries readable by breaking them into named steps instead of deeply nested subqueries.
Syntax: WITH active AS (SELECT ... WHERE active), totals AS (SELECT ... FROM active ...) SELECT * FROM totals. Win: readability (read top to bottom like a pipeline), reuse (reference the same CTE multiple times), and recursion. A recursive CTE (WITH RECURSIVE) handles hierarchies/graphs — org trees, comment threads, shortest path. Performance note: in older PostgreSQL (<12) a CTE was an "optimization fence" (always materialized) → it could be slower than a subquery; from 12 they're inlined when possible (MATERIALIZED/NOT MATERIALIZED control this). A SQL:1999 standard. Together with window functions, the backbone of readable analytical SQL.