COF-C03 Question 304
Single answerAggregate functionsA retail company stores order data in a table named ORDERS with the columns CUSTOMER_ID, REGION, ORDER_AMOUNT, and ORDER_DATE. Analysts need a report that returns one row per REGION showing: the number of orders, the total sales amount, and the average order amount for that region. They also want regions with no non-NULL ORDER_AMOUNT values to show NULL for the average rather than 0. Which SQL statement best meets this requirement?
- A
SELECT REGION, COUNT(*) AS ORDER_COUNT, SUM(ORDER_AMOUNT) AS TOTAL_SALES, AVG(ORDER_AMOUNT) AS AVG_ORDER_AMOUNT FROM ORDERS GROUP BY REGION;
- B
SELECT REGION, COUNT(ORDER_AMOUNT) AS ORDER_COUNT, SUM(ORDER_AMOUNT) AS TOTAL_SALES, AVG(NVL(ORDER_AMOUNT, 0)) AS AVG_ORDER_AMOUNT FROM ORDERS GROUP BY REGION;
- C
SELECT REGION, COUNT() AS ORDER_COUNT, SUM(ORDER_AMOUNT) AS TOTAL_SALES, ORDER_AMOUNT / COUNT() AS AVG_ORDER_AMOUNT FROM ORDERS GROUP BY REGION;
- D
SELECT REGION, COUNT(DISTINCT CUSTOMER_ID) AS ORDER_COUNT, SUM(ORDER_AMOUNT) AS TOTAL_SALES, AVG(ORDER_AMOUNT) AS AVG_ORDER_AMOUNT FROM ORDERS GROUP BY REGION;
Show answer and explanation
Correct answer: A
Explanation
This question tests practical use of aggregate functions with GROUP BY in Snowflake. The key behaviors are: COUNT() counts all rows, COUNT(column) counts only non-NULL values, SUM and AVG ignore NULL inputs, and AVG returns NULL when there are no non-NULL values to average. Therefore, the best query is the one using COUNT() for order count and AVG(ORDER_AMOUNT) without replacing NULLs. This aligns with Snowflake SQL aggregate function behavior and standard reporting best practices: preserve NULL semantics unless the business explicitly wants missing values treated as 0.
- A. Correct.
Correct. GROUP BY REGION returns one row per region. COUNT(*) counts all rows in each region, which matches the requirement for number of orders. SUM(ORDER_AMOUNT) adds non-NULL order amounts, and AVG(ORDER_AMOUNT) calculates the average over non-NULL values only. In Snowflake, AVG ignores NULLs, so if a region has no non-NULL ORDER_AMOUNT values, the result is NULL, which is exactly what the analysts want.
- B. Incorrect.
Incorrect. COUNT(ORDER_AMOUNT) counts only rows where ORDER_AMOUNT is not NULL, so it would undercount orders when some orders have NULL amounts. Also, AVG(NVL(ORDER_AMOUNT, 0)) converts NULL amounts to 0 before averaging, which changes the business meaning and would return 0 instead of NULL for regions with no non-NULL ORDER_AMOUNT values.
- C. Incorrect.
Incorrect. This expression is not valid for the stated aggregation goal because ORDER_AMOUNT is not aggregated and not included in the GROUP BY clause. In Snowflake, non-aggregated selected columns must appear in the GROUP BY clause. Even if rewritten, dividing a single ORDER_AMOUNT value by COUNT(*) would not correctly compute the regional average.
- D. Incorrect.
Incorrect. COUNT(DISTINCT CUSTOMER_ID) counts unique customers, not orders. A region can have multiple orders from the same customer, so this would not satisfy the requirement to return the number of orders. SUM and AVG are correct here, but the order count metric is wrong.