Level 8 · Debug: the win-back list is too short

The starter has two bugs that hide each other. Finding both, one at a time, is the skill this level practices.

Spotting the dropped rows

The quickest check for any LEFT JOIN report: compare the row count with the size of the left side. Q1 2025 has 126 sign-ups, but the starter returns 30 rows. Rows went missing, so a condition removed them.

For a customer with no matching order, the LEFT JOIN fills every o. column with NULL. The WHERE condition o.status = 'completed' is then NULL = 'completed', which is not true, and the row is dropped. The LEFT JOIN has quietly become an inner join. Any condition on the right table that should only limit what counts as a match belongs in ON:

LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'completed'
 AND o.order_date >= '2025-11-01'
 AND o.order_date < '2025-12-01'

Conditions on the left table, like the signup window, stay in WHERE, because they are meant to remove customers.

The second bug

After moving the filters, run it again. Now there are 126 rows, but the smallest nov_orders is 1. A customer with no match still has one row, padded with NULLs, and COUNT(*) counts rows. Count a column from the right table instead: COUNT(o.order_id) skips NULLs, so unmatched customers get 0. Of the 126, 96 placed no completed order in November.

Common mistakes

• Moving the signup filter into ON as well. On a LEFT JOIN, a condition on the left table in ON does not remove anyone, so every customer in the table would come back.
• Writing order_date <= '2025-11-30'. order_date is a timestamp, so an order at 8 PM on November 30 is later than midnight and is lost. Use < '2025-12-01'.
• Trusting a result because it “looks reasonable”. Thirty engaged customers sounded plausible; only the comparison with the 126 sign-ups showed the gap.

In SQL Server

Identical logic. If you want the zero shown explicitly in other contexts, SQL Server offers ISNULL(x, 0) next to the standard COALESCE(x, 0); COUNT already returns 0, so neither is needed here.