The Query That Returned Zero Rows
A support analyst is asked how many customers have never placed an order. She writes:
SELECT COUNT(*) FROM customers c
WHERE c.customer_id NOT IN (SELECT o.customer_id FROM orders o);
and gets 0, although the CRM export has 12,040 customers and the orders table has 210,000 rows. Separately, a dashboard tile labelled 'total customers' is built as COUNT(*) from customers c LEFT JOIN orders o ON o.customer_id = c.customer_id and reports 61,400. The warehouse team says orders.customer_id is nullable, and 1,850 order rows have no customer attached because they came through a guest-checkout flow. The dashboard refreshes hourly, and the analyst also notices counts dipping in the current hour. The support lead needs a defensible number for a board slide by end of day.
1. Explain why the NOT IN query returned 0 rows even though thousands of customers have no orders, and give two correct rewrites.
Read the worked answer
The mechanism is three-valued logic. x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. Any comparison with NULL yields UNKNOWN, and TRUE AND UNKNOWN is UNKNOWN. A row is only returned when the predicate is TRUE, so as soon as the subquery returns a single NULL - which it does here, because orders.customer_id is nullable - the whole predicate becomes UNKNOWN for every customer. The result is an empty set, not an error. This is the classic NULL trap, and it is silent.
Rewrite 1 - NOT EXISTS, which is NULL-safe and usually the clearer intent:
SELECT COUNT(*) FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
Rewrite 2 - filter the NULLs out of the subquery if the team prefers NOT IN:
SELECT COUNT(*) FROM customers c
WHERE c.customer_id NOT IN (
SELECT o.customer_id FROM orders o WHERE o.customer_id IS NOT NULL
);
A third option, a LEFT JOIN ... WHERE o.customer_id IS NULL, works but is easy to get wrong once additional join conditions are added. Whichever form is used, the analyst should sanity-check the answer: the never-ordered count plus the distinct-ordered count should equal 12,040. If it does not, there is a second defect - most likely orphan order rows whose customer_id does not exist in customers.
2. Explain the 61,400 figure on the 'total customers' tile and specify the corrected expressions for total customers, ordering customers, and orders.
Read the worked answer
61,400 is a count of joined rows, not of customers. customers LEFT JOIN orders emits one row per matching order, plus one row (with NULL order columns) for each customer with no orders. So COUNT(*) sums roughly 210,000 order rows minus the 1,850 guest rows, further restricted by whatever else the tile filters - and it happens to land at 61,400. It has no interpretation as a customer count. Notably, it is not even a reliable order count, because guest-checkout rows have no customer to join to and drop out.
Corrected expressions:
-- distinct customers present in the join
SELECT COUNT(DISTINCT c.customer_id)
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id;
-- customers who have ordered at least once
SELECT COUNT(DISTINCT o.customer_id)
FROM orders o WHERE o.customer_id IS NOT NULL;
-- order count, including guest checkout
SELECT COUNT(*) FROM orders;
COUNT(DISTINCT ...) is required whenever a one-to-many join is in the FROM clause. COUNT(column) would also work on the left table's key if the grain of customers is guaranteed unique, but that guarantee should be asserted, not assumed - the same duplicates that break joins break counts. If the tile needs both measures, compute them as separate expressions over separate queries rather than trying to derive one from the other; mixing grains in a single aggregate is where these 61,400-type numbers come from.
3. The analyst sees counts dipping in the current hour. What is the likely cause, and how should the board slide be framed so the number is defensible?
Read the worked answer
The dip is a completeness artefact, not a business signal. An hourly refresh over an append-only order stream will always show the current hour partially loaded - rows are still arriving from the checkout flow, and any timezone conversion in the BI layer can shift the boundary further. The current hour is therefore a partial bucket, and plotting it as a drop invites exactly the wrong reading. Two fixes: exclude the current, incomplete period, or mark it explicitly as partial with a data-freshness timestamp; and align the timezone between the warehouse timestamps and the reporting layer.
Framing for the board. State the metric definition on the slide - 'customers with at least one completed order in the trailing twelve months' - because 'total customers' means several different things to several different people. Report the as-of timestamp. Add the never-ordered count from the corrected NOT EXISTS query as a companion figure; it is more decision-useful than the raw total and it demonstrates the metric was validated. Present guest-checkout orders as a separate, clearly labelled line rather than dropping them, since excluding 1,850 rows without saying so is the kind of omission a board member will eventually find.
Finally, reconcile against the CRM export once, and record the difference and its cause. A number that does not tie to a source system needs an explanation before it needs a chart.