SQL · Queries that survive scale

What is a CTE and why would you use one?

Mediumctereadabilitywith clauseUpdated Oct 2026

Short answer

A common table expression is a named query defined with WITH and used by the main statement. It improves readability, supports recursion, and can isolate a reusable intermediate result.

A CTE is a named subquery scoped to one statement. It is not automatically a temporary table: depending on the engine and version, it may be inlined or materialized. Treat it as a way to structure a query, not as a guaranteed performance optimization.

sql

WITH monthly_revenue AS (
  SELECT date_trunc('month', created_at) AS month,
         SUM(total_cents) AS revenue_cents
  FROM orders
  WHERE status = 'paid'
  GROUP BY 1
)
SELECT month, revenue_cents
FROM monthly_revenue
WHERE revenue_cents > 100000
ORDER BY month;

When a CTE helps

Breaking a long query into named steps that reviewers can follow
Reusing the same intermediate result in more than one place
Recursive queries that reference themselves
Separating a window or aggregate calculation from the filter applied to it

In PostgreSQL 12 and later, a non-recursive CTE referenced once is usually inlined unless MATERIALIZED is specified. MySQL and SQL Server have their own rules. If performance depends on materialization, verify it in the execution plan.

A CTE referenced only once that does not improve clarity is often an extra layer. Compare the plan with the equivalent subquery before adding one for perceived speed.

Follow-ups you should expect

Interviewers use these to check whether the first answer was understanding or memorisation.

1Is a CTE always materialized?
2How does a CTE differ from a view?
3When would a CTE make a query slower?
4How do you filter on a window function result?

Say it in the interview

A CTE is a named query introduced with WITH and available to the statement that follows. I use it to break complex logic into readable steps, reuse an intermediate result, or write recursive queries. It is not automatically faster, because many engines inline a single-use CTE. I check the plan when materialization matters, and I avoid adding CTEs that only wrap a trivial query.

Use this as a model, not a script. Saying it in your own words is what makes it survive follow-up pressure.

Now say it out loud.

Understanding an answer and delivering it under pressure are different skills. Rehearse this topic in a live SQL mock interview and get scored feedback on depth, clarity and confidence.

Start a free SQL interview Browse all questions

When a CTE helps

Follow-ups you should expect

Related questions

Now say it out loud.