COF-C03 Question 305
Single answerAggregate functionsA retail company stores order data in a Snowflake table named ORDERS with the columns CUSTOMER_ID, ORDER_ID, REGION, and ORDER_TOTAL. Analysts need a query that returns one row per REGION showing the average order value, but only for regions that have more than 100 orders. Which SQL statement correctly meets this requirement?
- A
SELECT REGION, AVG(ORDER_TOTAL) AS AVG_ORDER_VALUE FROM ORDERS WHERE COUNT(ORDER_ID) > 100 GROUP BY REGION;
- B
SELECT REGION, AVG(ORDER_TOTAL) AS AVG_ORDER_VALUE FROM ORDERS GROUP BY REGION HAVING COUNT(ORDER_ID) > 100;
- C
SELECT REGION, AVG(ORDER_TOTAL) AS AVG_ORDER_VALUE FROM ORDERS HAVING COUNT(ORDER_ID) > 100 GROUP BY REGION;
- D
SELECT REGION, COUNT(ORDER_ID), AVG(ORDER_TOTAL) AS AVG_ORDER_VALUE FROM ORDERS WHERE COUNT(ORDER_ID) > 100;
Show answer and explanation
Correct answer: B
Explanation
The key concept is the difference between WHERE and HAVING when working with aggregate functions in Snowflake SQL. WHERE filters rows before aggregation, while HAVING filters groups after aggregate calculations are performed. Because the requirement is to return only regions with more than 100 orders, the filter must be applied to COUNT(ORDER_ID) in the HAVING clause. Snowflake follows standard SQL behavior for GROUP BY and HAVING: any non-aggregated column in the SELECT list must be included in GROUP BY, and aggregate conditions belong in HAVING rather than WHERE. This is a common applied scenario on the SnowPro Core exam because it tests practical understanding of how aggregate functions are used in reporting queries.
- A. Incorrect.
Incorrect. COUNT(ORDER_ID) is an aggregate function, and aggregate filters cannot be placed in the WHERE clause. WHERE filters individual rows before grouping occurs. In Snowflake, conditions on grouped results must be placed in the HAVING clause after GROUP BY.
- B. Correct.
Correct. This query groups rows by REGION, calculates AVG(ORDER_TOTAL) for each group, and uses HAVING COUNT(ORDER_ID) > 100 to keep only regions with more than 100 orders. This matches the requirement of returning one row per region with an aggregate filter applied to the grouped result set.
- C. Incorrect.
Incorrect. In SQL syntax, HAVING is evaluated after GROUP BY logically, but in the written query, GROUP BY must come before HAVING. This option reflects a common misunderstanding between logical query processing order and valid SQL statement syntax.
- D. Incorrect.
Incorrect. This query has multiple issues: it uses COUNT(ORDER_ID) in the WHERE clause, which is not valid for aggregate filtering, and it does not include a GROUP BY clause even though REGION is selected alongside aggregates. In Snowflake, non-aggregated selected columns must appear in the GROUP BY clause.