Which query returns customers whose average order amount exceeds 100?

• A. SELECT customer_id, AVG(amount) FROM orders WHERE AVG(amount) > 100 GROUP BY customer_id;
• B. SELECT customer_id, AVG(amount) FROM orders GROUP BY customer_id HAVING AVG(amount) > 100;
• C. SELECT customer_id, AVG(amount) AS avg_amt FROM orders GROUP BY customer_id;
• D. SELECT customer_id, MAX(amount) FROM orders GROUP BY customer_id HAVING MAX(amount) > 100;

Answer: (B)

Filtering grouped results using a condition on an aggregate after grouping is the key idea. To get the average order amount per customer, you first group rows by each customer, then apply a filter to keep only those groups whose average amount exceeds 100. In SQL, that filter must be in a HAVING clause because it references the result of an aggregation (the average). So grouping by customer_id and then using HAVING AVG(amount) > 100 selects exactly the customers whose average order amount is above 100.

The other options fail for common reasons: placing a condition like AVG(amount) > 100 in WHERE tries to filter before aggregation, which isn’t allowed; omitting the filter returns all customers regardless of their average; and using MAX(amount) checks the maximum single order instead of the average, changing the meaning entirely.