SnowPro Associate: Platform Question 169
Single answer● Run basic SQL commandsA data analyst needs a quick report showing the total number of orders and the average order amount for each customer in the ORDERS table. The table contains the columns CUSTOMER_ID, ORDER_ID, and ORDER_AMOUNT. The analyst only wants customers who have placed more than 5 orders. Which SQL statement will return the correct result in Snowflake?
- A
SELECT CUSTOMER_ID, COUNT(ORDER_ID) AS TOTAL_ORDERS, AVG(ORDER_AMOUNT) AS AVG_ORDER_AMOUNT FROM ORDERS WHERE COUNT(ORDER_ID) > 5 GROUP BY CUSTOMER_ID;
- B
SELECT CUSTOMER_ID, COUNT(ORDER_ID) AS TOTAL_ORDERS, AVG(ORDER_AMOUNT) AS AVG_ORDER_AMOUNT FROM ORDERS GROUP BY CUSTOMER_ID HAVING COUNT(ORDER_ID) > 5;
- C
SELECT CUSTOMER_ID, COUNT(ORDER_ID) AS TOTAL_ORDERS, AVG(ORDER_AMOUNT) AS AVG_ORDER_AMOUNT FROM ORDERS HAVING COUNT(ORDER_ID) > 5 GROUP BY CUSTOMER_ID;
- D
SELECT CUSTOMER_ID, TOTAL_ORDERS, AVG(ORDER_AMOUNT) AS AVG_ORDER_AMOUNT FROM ORDERS GROUP BY CUSTOMER_ID HAVING TOTAL_ORDERS > 5;
Show answer and explanation
Correct answer: B
Explanation
This question tests practical use of basic SQL commands in Snowflake, specifically aggregation with COUNT and AVG, grouping with GROUP BY, and filtering aggregated results with HAVING. The correct query pattern is: SELECT grouped columns and aggregate functions, FROM the source table, GROUP BY the non-aggregated columns, and use HAVING to filter based on aggregate results. In Snowflake, WHERE is evaluated before grouping and cannot be used to filter on aggregate values such as COUNT(). This aligns with standard SQL behavior and Snowflake documentation for SELECT, GROUP BY, and HAVING.
- A. Incorrect.
Incorrect. COUNT(ORDER_ID) is an aggregate function, and aggregate filtering must be done with HAVING, not WHERE. In Snowflake SQL, WHERE filters rows before grouping, while HAVING filters grouped results after aggregation. This option reflects a common mistake when writing summary queries.
- B. Correct.
Correct. This query correctly groups rows by CUSTOMER_ID, calculates COUNT(ORDER_ID) and AVG(ORDER_AMOUNT) for each group, and then uses HAVING COUNT(ORDER_ID) > 5 to return only customers with more than 5 orders. This is the proper pattern for basic aggregation and post-aggregation filtering in Snowflake SQL.
- C. Incorrect.
Incorrect. The clause order is wrong. In SQL, including Snowflake, GROUP BY must come before HAVING. Although the logic of using HAVING with COUNT is conceptually right, the statement itself is not valid because the clauses are in the wrong order.
- D. Incorrect.
Incorrect. TOTAL_ORDERS is referenced in the SELECT list without being defined in the FROM source, and this query attempts to use the alias in a way that does not create the aggregate expression. The query should explicitly calculate COUNT(ORDER_ID) AS TOTAL_ORDERS. This option represents a misunderstanding of how column aliases and aggregate expressions work.