When do you use a SQL CTE?

Direct answer

What a strong answer covers

How it works

A common table expression gives a name to an intermediate result so a query reads top to bottom as a sequence of steps instead of nested subqueries. Recursive CTEs walk hierarchies such as org charts or bill-of-materials trees. Whether the engine materialises a CTE or inlines it depends on the database: PostgreSQL inlines by default since version 12 unless MATERIALIZED is specified, some engines always materialise. So a CTE is a readability and structure tool first, and its performance effect must be checked in the plan rather than assumed.

Example

A churn analysis defines active_users, then churned_users as those in the previous month but not this one, then joins to plan data; each step is a named CTE that an analyst can run and inspect alone. A recursive CTE expands a category tree from parent_id to produce every descendant of a root category for a filter.

Common mistakes

• Assuming a CTE caches a result and referencing it many times in an engine that recomputes it each time.
• Writing a recursive CTE with no termination condition on cyclic data.
• Using a CTE to replace a view that many queries need, and duplicating the logic.

A useful answer structure

Answer from your own work.

Do not memorise a script: use only examples you can explain honestly in follow-up questions.