CTE vs subquery vs temp table

A CTE is mostly about readability. A subquery is a one-off inline piece. A temp table is real stored data that exists for your session. The first two are usually just text the optimizer folds into one plan. The third is physically written out once and reused.

What each one is good for

• CTE (WITH x AS (...)): names a step so a long query reads top to bottom. Best default for multi-step logic.
• Subquery: fine for a single use, such as WHERE id IN (SELECT ...). Gets hard to read once nested.
• Temp table: you run CREATE TEMP TABLE t AS SELECT ... once, and later statements read from t. You can index it or gather statistics on it in engines that support that.

The part interviewers care about: is a CTE computed once?

It depends on the engine, and the answer is often "not necessarily".

• Postgres 12 and later: a CTE that is referenced once is inlined, so it behaves like a subquery. A CTE referenced twice or more is computed once and reused, unless you write AS NOT MATERIALIZED. You can force the old behaviour with AS MATERIALIZED.
• BigQuery: a CTE referenced several times may be evaluated several times. BigQuery's own docs say it does not guarantee materialisation.
• Other engines differ, so look at the plan.

So if an expensive CTE is used twice and the engine recomputes it, you pay twice. That is when a temp table (or a materialised intermediate table) is the better choice.

CREATE TEMP TABLE daily_totals AS
SELECT order_date, SUM(amount) AS revenue
FROM orders
GROUP BY order_date;

-- reused by several statements below
SELECT * FROM daily_totals WHERE revenue > 10000;

Rule of thumb

Start with a CTE. Move to a temp table when the step is expensive, used several times, or when you want to check intermediate results while debugging. Temp tables only live for the session, so inside a scheduled warehouse job you may use a transient table that gets dropped at the end.