Level 3 · App orders per business customer

This is the most common join bug in analytics, and the starter query has it. The core idea: a WHERE condition on the right table of a LEFT JOIN throws away the unmatched rows, so the LEFT JOIN acts like an inner join. Run the starter, then count the business customers: it returns fewer rows than there are business customers.

What goes wrong

SELECT c.customer_id, COUNT(o.order_id) AS app_orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE c.segment = 'business'
  AND o.channel = 'app'          -- the bug
GROUP BY c.customer_id;

Follow the logical order. The LEFT JOIN runs first and correctly keeps customers with no orders, with NULLs for every order column. It also keeps customers whose only orders were on the web, one row per web order. Then WHERE o.channel = 'app' runs. For both groups, o.channel is NULL or ‘web’, so the condition is not true and the rows are removed. The customers Daniel cares most about, the ones with zero app orders, are exactly the ones that vanish.

A WHERE condition on the right table of a LEFT JOIN turns it into an INNER JOIN.

The fix: filter in ON

Conditions in ON decide which right-side rows match. They never remove left-side rows:

SELECT c.customer_id, COUNT(o.order_id) AS app_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.channel = 'app'
WHERE c.segment = 'business'
GROUP BY c.customer_id;

Now each business customer is matched only to their app orders. If there are none, the customer is kept with NULLs, and COUNT(o.order_id) counts zero non-NULL values, giving 0.

The condition on the left table, c.segment = 'business', correctly stays in WHERE: it is meant to remove customers. Putting it in ON would instead keep every customer and just fail to match orders for consumers, so consumers would show up with 0.

condition about	goes in
which left rows to keep	WHERE
which right rows count as a match	ON

COUNT(*) would also be wrong

With the fix in place, try COUNT(*). Customers with no app orders now show 1, because the LEFT JOIN produced one row for them (full of NULLs). Count a column from the right table instead.

Interview note

“Why did my LEFT JOIN lose rows?” is a staple of SQL interviews and take-home reviews. Explain it in terms of NULLs: the unmatched rows have NULL in the right-side columns, a WHERE comparison with NULL is unknown, and WHERE keeps only true rows. Then give the fix: move the condition into ON, or filter the right table in a subquery before joining.