Level 9 · Debug: everyone is a new customer

The order of operations inside a query decides what an aggregate can see. WHERE runs before GROUP BY, so a filter in the CTE changes what MIN is computed over.

Spotting the bug

A sanity check exposes it quickly: the starter claims 253 new business customers in 2025, while the whole table has 286 business customers, and 192 of them signed up before 2025. They cannot almost all be new in the same year. Whenever a “new” count comes close to the total population, suspect the definition before celebrating the result.

Ask what one row of the CTE means. With the 2025 filter, first_order_date is “first completed order in 2025”, not “first completed order ever”. Every customer who bought in 2025 has one, so the outer query counts all 2025 buyers.

The fix: aggregate on full history, filter after

WITH first_orders AS (
  SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  WHERE status = 'completed'
  GROUP BY customer_id
)
SELECT COUNT(*) AS new_in_2025
FROM first_orders
WHERE first_order_date >= '2025-01-01'
  AND first_order_date < '2026-01-01';

This returns 635 customers whose very first completed order was in 2025, against 1,447 customers who bought in 2025 at all. The status filter stays inside the CTE: a cancelled order is not a purchase, so it should not count as anyone’s first.

The same reasoning applies to any “first” or “last” measure: first product bought, first coupon used, date of the last order before churn. Each one is computed over all history.

A useful rule: filters that define what counts (completed orders) go before the aggregate. Filters that define which result rows you report (the 2025 window) go after it.

Common mistakes

• Moving the date filter into HAVING MIN(order_date) >= '2025-01-01'. That also works, because HAVING runs after the grouping; just do not leave it in WHERE.
• Dropping the status filter along with the date filter. A customer whose first order was cancelled in 2024 and who then bought in 2025 is new in 2025.

In SQL Server

The same CTE works. To compare on the calendar date of a datetime2 column, use CAST(first_order_date AS date) or keep the half-open range >= '20250101' AND < '20260101', which is correct for any time of day.