Level 3 · The high-value list that was too long

The starter does not just pick the wrong customers. It changes the numbers it reports for them, which is why the list looked convincing.

What WHERE does here

WHERE runs before GROUP BY. In the starter it removes every order of $400 or less, and only then groups and averages what is left. The question it answers is “who placed at least one order over $400, and what did those big orders average?” That is a different list.

Follow one customer through both versions. Customer 87 had 4 completed orders in 2025 with an average of $348.73. The starter keeps only their 2 orders above $400, reports orders = 2 and avg_order_value = 474.99, and invites them. The true average is below the bar.

The fix

Move the condition to HAVING, which filters groups after they are formed:

SELECT customer_id,
       COUNT(*) AS orders,
       ROUND(AVG(order_total), 2) AS avg_order_value
FROM order_totals
GROUP BY customer_id
HAVING AVG(order_total) > 400;

The list drops from 784 customers to 572. Out of 1,447 customers with a completed 2025 order, that is the real trade program audience.

How to spot it

• A filter in WHERE on the same quantity you then aggregate (order_total, quantity, amount) is worth a second look.
• Check one customer by hand: list all their orders without any filter and compute the average.
• Compare counts: if orders in the result is lower than what the customer really placed, rows were filtered before grouping.

Common mistakes

• Writing WHERE AVG(order_total) > 400: that one fails loudly, because aggregates are not allowed in WHERE. The silent version is filtering the raw column instead.
• Keeping both filters. WHERE order_total > 400 plus HAVING still averages only the big orders.
• Filtering by the rounded alias in HAVING: fine in DuckDB, but repeat the expression for portability.

In SQL Server

T-SQL has the same WHERE and HAVING rules, and it does not allow an alias in HAVING either:

GROUP BY customer_id
HAVING AVG(order_total) > 400;

Interview note

“Customers whose average order value is above X” is a favorite screening question precisely because the WHERE version runs and returns plausible rows.