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 is after two things: do you reach for CTEs for the right reasons — readability and reuse — and do you know that the popular “CTEs are always faster because they run once” claim is, in most modern databases, simply wrong.

CTEs win on readability

Deeply nested subqueries are hard to read, harder to debug, and easy to get subtly wrong. A CTE lets you name each step:

-- Nested subqueries — you read inside-out
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 named CTEs — top to bottom, each step labelled
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;

A worked example — CTEs win on reuse

The other real advantage: a CTE can be referenced more than once. A subquery you need in two places has to be copy-pasted; a CTE doesn’t:

WITH ranked AS (
    SELECT name, dept, salary,
           RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk
    FROM employees
)
SELECT r1.name AS top_earner, r2.name AS runner_up, r1.dept
FROM ranked r1
JOIN ranked r2 ON r1.dept = r2.dept AND r1.rk = 1 AND r2.rk = 2
ORDER BY r1.dept;

top_earner	runner_up	dept
Aarav	Chen	Eng
Guo	Farah	Ops
Eli	Dara	Sales

The ranked CTE is referenced twice — once as r1 for each department’s top earner, once as r2 for the runner-up — and the window logic is written exactly once. As nested subqueries you’d have to paste that RANK() OVER (...) block into both halves of the join.

Performance: not “always faster”

Here’s the myth, dismantled. In PostgreSQL before v12, a CTE was an optimization fence — the planner materialised it and couldn’t push predicates through, which sometimes made CTEs slower than the equivalent subquery. From PostgreSQL 12+ (and in Snowflake, BigQuery, SQL Server) non-recursive CTEs are inlined by default: the optimizer treats them like subqueries and applies the same predicate pushdown and index choices.

If you genuinely want to materialise one — to avoid recomputing an expensive aggregate referenced many times — say so explicitly: WITH cte AS MATERIALIZED (...) in PostgreSQL 12+, or a temp table.