CTE vs temporary table

Question

Accepted Answer

A CTE lives only for the single statement it is attached to. If you need the result across multiple queries, you must recreate the CTE each time - potentially running the expensive aggregation repeatedly. A temp table (CREATE TEMP TABLE ... AS SELECT ...) materializes the result once and is accessible for the rest of the session. You can even index it. Use CTEs for readability within one query; use temp tables when you need persistence or want to guarantee a single execution.