Use a CTE when readability matters or when the same intermediate result is referenced more than once, since it names a step instead of nesting it. Subqueries are fine for a single small inline filter. Performance is roughly equivalent on most modern engines, though some materialize CTEs and some inline them, so a CTE is not automatically faster or slower than the equivalent nested query.
Why interviewers ask this
This is partly a craft question and partly a myth check, because plenty of candidates confidently claim that CTEs are faster or that they always materialize. Interviewers want the honest answer that the main benefit is legibility and reuse, plus awareness that engines differ in how they plan them, and that recursive CTEs solve hierarchy problems a plain subquery cannot.
How to structure your answer
- Lead with readability and reuse as the real reasons.
- Correct the myth that CTEs are inherently faster.
- Note engine differences in materialization.
- Mention recursive CTEs for hierarchies.
- Say when a plain subquery is genuinely the better choice.
Example answer
Mostly for readability and reuse. If a query has four logical steps, four named CTEs read like a paragraph and a reviewer can follow the reasoning, whereas three levels of nested subqueries mean starting in the middle and working outward. The other real reason is referencing the same intermediate set twice without repeating it. What I would not claim is that CTEs are faster. That myth is everywhere. Depending on the engine and version, a CTE may be inlined into the plan or materialized once, and either can be the faster option depending on how many times it is referenced and how selective it is. On Postgres before version 12, CTEs were an optimization fence, which occasionally made them dramatically slower, so I check the plan when performance actually matters. Where a CTE is uniquely useful is recursion: walking a manager hierarchy or a category tree is straightforward with a recursive CTE and genuinely awkward without one.
Walking into this interview soon? GhostPilot listens to your live call, spots the question the moment it is asked, and puts a structured answer on your screen in real time. Try it on your next mock, or grab a $29 Session Pass, no subscription, for the real thing.
See how it worksFollow-up questions to expect
- What is a recursive CTE and what would you use it for?
- How would you check whether a CTE is hurting your query plan?
- When would a temporary table beat a CTE?
Related data analyst questions
Your interviewer will ask their own version of this. Paste your actual job description into the free Question Predictor and get the 20 questions that role is most likely to ask, with what each one is really probing.
Predict my questions