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

Answer each one yourself before you look: write your query or your reasoning in the box, or open the playground and run it. Then reveal the model answer and compare. Nothing you type is saved or sent anywhere.

1. Build a daily shipped-revenue report from orders and line items.

   Your answer Try it in the playground →
   Reveal a model answer

   with shipped_lines as (
     select 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'
   )
   select order_date,
          sum(line_revenue) as revenue
   from shipped_lines
   group by order_date
   order by order_date

   One CTE per grain: shipped line items, then one row per day. Each step can be run on its own to check it.

2. Find customers whose lifetime revenue is above the average customer revenue.

   Your answer Try it in the playground →
   Reveal a model answer

   with customer_revenue as (
     select o.customer_id,
            sum(oi.quantity * oi.unit_price) as revenue
     from orders as o
     join order_items as oi on oi.order_id = o.order_id
     group by o.customer_id
   )
   select customer_id, revenue
   from customer_revenue
   where revenue > (select avg(revenue) from customer_revenue)
   order by revenue desc, customer_id

   Name the per-customer totals once, then compare each to the average of those totals. The average of customers, not of line items: say which you mean. Customers with no orders are not in this average; ask whether they should count as zero.

3. Rank products after first aggregating revenue by product.

   Your answer Try it in the playground →
   Reveal a model answer

   with product_revenue as (
     select p.product_name,
            sum(oi.quantity * oi.unit_price) as revenue
     from order_items as oi
     join products as p on p.product_id = oi.product_id
     group by p.product_name
   )
   select product_name,
          revenue,
          rank() over (order by revenue desc) as revenue_rank
   from product_revenue
   order by revenue_rank, product_name

   Aggregate in the CTE, rank in the outer query. Ranking inside the aggregation step is possible but harder to read and to debug.

4. Debug a query by exposing row counts after each join step.

   Your answer Try it in the playground →
   Reveal a model answer

   with base as (
     select * from orders
   ),
   with_items as (
     select b.order_id, oi.product_id
     from base as b
     join order_items as oi on oi.order_id = b.order_id
   )
   select 'orders' as step, count(*) as rows from base
   union all
   select 'orders + items', count(*) from with_items

   Give each step a name and count its rows. A jump from 24 to 48 is the fan-out made visible: two line items per order on average. That habit locates a bug faster than rereading the final query.

5. Explain when you would use a CTE instead of a nested subquery.

   Your answer
   Reveal a model answer

   When a step deserves a name, is used more than once, or makes the query read top to bottom. A one-line scalar comparison is often clearer inline. Performance is engine-specific, since some inline CTEs and some materialize them, so lead with readability and check the plan if speed matters.

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, while daily_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.