SQL interview question: find the bugs in a churn query

MediumCode review

The colleague’s query returns an empty table. For each acquisition channel, find August 2026 buyers who bought nothing in September: their number, August orders and August revenue.

WITH august_orders AS (
  SELECT o.user_id,
         u.acq_channel,
         o.order_id,
         o.amount_usd
  FROM orders AS o
  JOIN users AS u
    ON u.user_id = o.user_id
  WHERE o.status = 'delivered'
    AND o.created_at >= '2026-08-01'
    AND o.created_at < '2026-08-31'
),
september_buyers AS (
  SELECT user_id
  FROM orders
  WHERE status = 'delivered'
    AND created_at >= '2026-09-01'
)
SELECT acq_channel,
       COUNT(DISTINCT user_id) AS churned_buyers,
       COUNT(*) AS august_orders,
       SUM(amount_usd) AS august_revenue_usd
FROM august_orders
WHERE user_id NOT IN (SELECT user_id FROM september_buyers)
GROUP BY acq_channel

Example

Buyers 1 and 2 churned: they bought only in August. Buyer 3 bought in September too.

Skills it tests

• Reading SQL
• Edge cases

More interview questions

• SQL: Second-highest price in each groupEasy · Nth-highest value
• SQL: Top 3 categories in each cityMedium · Top-N per group
• SQL: Day-1 retention by channelMedium · Retention
• SQL: Find the bugs in a revenue queryMedium · Code review
• Python: Merge overlapping intervalsMedium · Intervals

All questions