Reference

CTE and Subquery Tradeoffs

Interviewers evaluate reasoning quality, not just syntax. This lesson helps you justify CTE vs subquery decisions.

Decision Framework #

Use CTEs when:

you need step-wise logic clarity
multiple downstream references improve readability
debugging intermediate outputs matters

Use subqueries when:

logic is simple and local
a one-off nested operation is sufficient
extra named steps add noise

Prompt #

Return customers whose monthly spend is above their average for the prior 3 months (excluding current month).

Loading SQL editor...

Honest caveat to state in the interview: ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING averages the customer's prior three active months, not the prior three calendar months. When a customer skips a month, the window silently reaches further back in time. If the requirement is strictly calendar-based, densify first with a month spine (cross join customers to a generated month series, defaulting missing months to 0) so every calendar month is present before applying the window.

Validation Check #

sql

-- First month per customer should have no prior-window average
WITH monthly AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', order_date) AS month_start,
    SUM(order_total) AS monthly_spend
  FROM ecom_orders
  WHERE order_status = 'completed'
  GROUP BY customer_id, DATE_TRUNC('month', order_date)
),
windowed AS (
  SELECT
    customer_id,
    month_start,
    AVG(monthly_spend) OVER (
      PARTITION BY customer_id
      ORDER BY month_start
      ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
    ) AS prior_3m_avg
  FROM monthly
)
SELECT *
FROM windowed
WHERE prior_3m_avg IS NOT NULL
  AND month_start = (
    SELECT MIN(m2.month_start)
    FROM monthly m2
    WHERE m2.customer_id = windowed.customer_id
			);

Scoring Rubric #

correctness of prior-window logic (40%)
clarity of CTE vs subquery tradeoff reasoning (25%)
validation quality (20%)
communication and assumptions (15%)

Tip

A clear, correct query with explicit tradeoffs beats an over-optimized but opaque answer.