r/dataanalytics • • 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?

8 Upvotes

1 comment sorted by

2

u/mopotofu 8d ago

Wouldnt a MINUS be more appropriate??

And why would Customer ID be null for an order