SQL Practice 26/26

Capstone: who has churned

Every lesson in this module has taught you one trap at a time. This one asks a single, ordinary-sounding business question and makes you use three of those lessons at once, because leaving any one of them out gives you a wrong answer that still looks plausible.

Churned means: no order on or after 2026-04-01, including a customer who has never placed an order at all. That reporting date is fixed - treat it as "today" for this exercise, not something to compute. A live date('now') would make the right answer drift depending on when the query happens to run, so the date is spelled out as a literal string instead.

Here's why each trap matters on its own. Group by customer_id and join orders normally, and a customer with zero rows in orders never enters the grouped result at all - they're not "not churned", they simply don't exist in the output, so the report undercounts churn without any error to notice. Use MIN(order_date) instead of MAX(order_date), and a customer who ordered constantly but stopped in March reads as active because their very first order was recent. And once you fix the first trap with a LEFT JOIN, the customer with no orders shows up with order_date as NULL on every column

so MAX(order_date) for them is NULL, and NULL < '2026-04-01' is NULL, not true. HAVING keeps only true, so that customer quietly drops right back out unless you check for NULL explicitly.

customers: five people. orders: Ana ordered recently, Luka only ever ordered back in January, Marta ordered twice - once in November and once in June, and only the June order should count - Elin's only order is from last October, and Ben has never placed one.

Your task: return the name of every churned customer - no order on or after 2026-04-01, including anyone with no orders at all. The answer is Luka, Elin, and Ben.

You'll practice:

Using LEFT JOIN so customers with zero matching rows survive a GROUP BY
Aggregating with MAX() to judge recency instead of first activity
Handling the NULL a LEFT JOIN produces inside HAVING, not just WHERE

-- Reporting date is frozen at 2026-04-01. Return the name of every churned customer (no order on or after that date, including customers with no orders at all).
SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
HAVING MAX(o.order_date) < '2026-04-01';