When should you choose a CTE over a subquery, and does a CTE always offer a performance advantage?

How to think about it

The interviewer wants to know two things: do you reach for CTEs for the right reason (readability, reuse), and do you know the performance myth — the idea that CTEs are “always faster because they run once” — is actually wrong in most modern databases?

CTEs win on readability

Deeply nested subqueries are hard to read, hard to debug, and easy to get wrong. CTEs let you name each step, which makes the logic easier to follow and review.

-- Hard to follow as nested subqueries
SELECT *
FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM (SELECT * FROM orders WHERE status = 'completed') completed
    GROUP BY customer_id
) totals
WHERE total > 1000;

-- Same logic as a CTE: each step is named and readable
WITH completed_orders AS (
    SELECT * FROM orders WHERE status = 'completed'
),
customer_totals AS (
    SELECT customer_id, SUM(amount) AS total
    FROM completed_orders
    GROUP BY customer_id
)
SELECT *
FROM customer_totals
WHERE total > 1000;

CTEs win on reuse (the other real advantage)

A subquery used in two places means you copy-paste the logic. A CTE can be referenced multiple times in the same query without duplication:

Performance: CTEs are not “always faster”

In PostgreSQL before v12, CTEs were optimization fences — the planner materialized them as a temporary result and could not push predicates through. This sometimes made CTEs slower than equivalent subqueries.

From PostgreSQL 12+ onward, non-recursive CTEs are inlined by default. Snowflake, BigQuery, and SQL Server also inline CTEs by default. The optimizer treats them like subqueries and applies the same predicate pushdown and index selection.

If you genuinely need to materialize a CTE (to avoid recomputing an expensive aggregation referenced many times), use WITH cte AS MATERIALIZED (...) in PostgreSQL 12+ or an explicit temp table.