SQL CTE Interview Questions

CTE questions test whether you can break a report into named, checkable steps. Interviewers usually care less about the word WITH and more about whether your query stays readable as the logic grows.

A good CTE answer gives each step one job: filter the base rows, join the needed tables, aggregate at the correct grain, then format or rank the final result. If you can explain each CTE in one sentence, the query is probably shaped well.

Common CTE interview prompts

1. Build a daily shipped-revenue report from orders and line items.
2. Find customers whose lifetime value is above the average customer value.
3. Rank products after first aggregating revenue by product.
4. Debug a query by exposing row counts after each join step.
5. Explain when you would use a CTE instead of a nested subquery.

with shipped_lines as (
  select o.order_id,
         o.order_date,
         oi.quantity * oi.unit_price as line_revenue
  from orders as o
  join order_items as oi on oi.order_id = o.order_id
  where o.status = 'shipped'
),
daily_revenue as (
  select order_date,
         sum(line_revenue) as revenue
  from shipped_lines
  group by order_date
)
select order_date, revenue
from daily_revenue
order by order_date

What matters

CTEs make intermediate result grains visible. In the example above,shipped_lines is one row per shipped line item, whiledaily_revenue is one row per order date. Naming those grains out loud helps prevent accidental double counting.

CTEs are also useful for debugging. If a final count looks wrong, run the CTE body by itself or add a short count query after the join step. This proves whether the problem came from filtering, joining, or grouping.

with joined as (
  select o.order_id,
         oi.product_id,
         oi.quantity
  from orders as o
  join order_items as oi on oi.order_id = o.order_id
)
select count(*) as joined_rows,
       count(distinct order_id) as orders
from joined

How to talk it through

Keep the explanation simple: "I will use one CTE to isolate shipped line items, then a second CTE to aggregate to one row per day. The final SELECT only sorts and returns the report."

If the interviewer asks about performance, keep the answer measured. CTEs are primarily a readability tool in most interview queries. The database may inline or optimize them differently by dialect, so the first priority is a correct result shape that can be checked.

Practice next

Work through common table expressions, the CTE reference, and the subqueries and CTEs cheatsheet. For applied practice, try the daily revenue report and revenue audit mission.