When you need the same result in two places

Question

Accepted Answer

The name is the key advantage: write WITH recent_orders AS (...) once, then reference recent_orders in both the main SELECT and a JOIN. With subqueries you would embed the same SELECT twice. Whether the CTE actually executes once or twice depends on the optimizer - PostgreSQL inlines non-recursive CTEs by default since version 12, so materialization is not guaranteed. The readability benefit is real regardless of execution strategy.