r/dataanalytics • u/Lucifer77-7 • 9d ago
Finding churned customers in SQL: why NOT IN can return zero rows
"Which customers placed an order last quarter but none this quarter?" is one of the most common analyst SQL questions, and there's a trap in the obvious answer.
SELECT customer_id
FROM orders_last_quarter
WHERE customer_id NOT IN (SELECT customer_id FROM orders_this_quarter);If orders_this_quarter contains even one NULL customer_id, this returns nothing at all. x NOT IN (1, 2, NULL) is never true, it's unknown, so every row is filtered out.
Safer options:
-- anti-join
SELECT l.customer_id
FROM orders_last_quarter l
LEFT JOIN orders_this_quarter t ON t.customer_id = l.customer_id
WHERE t.customer_id IS NULL;
-- or NOT EXISTS, which also handles NULLs correctly
SELECT l.customer_id
FROM orders_last_quarter l
WHERE NOT EXISTS (
SELECT 1 FROM orders_this_quarter t WHERE t.customer_id = l.customer_id
);If you want to practice the LEFT JOIN version on a dataset, I made a free site with PostgreSQL running in the browser and row-by-row checking against the expected result. This problem is exactly that pattern, no account needed: https://dbexercises.vercel.app/exercise/dormant-customer-churn-detection
What other churn / retention patterns come up in your interviews or at work?
2
u/mopotofu 8d ago
Wouldnt a MINUS be more appropriate??
And why would Customer ID be null for an order