SnowPro Associate: Platform Question 39
Single answer○ SQLA data engineer needs to produce a daily sales report from a Snowflake table named SALES_TXN. The report must show one row per customer with the customer's total sales amount for the previous day, but only for customers whose previous-day total exceeds 1000. Which SQL statement correctly returns the required result?
- A
SELECT CUSTOMER_ID, SUM(AMOUNT) AS TOTAL_SALES FROM SALES_TXN WHERE TXN_DATE = CURRENT_DATE - 1 AND SUM(AMOUNT) > 1000 GROUP BY CUSTOMER_ID;
- B
SELECT CUSTOMER_ID, SUM(AMOUNT) AS TOTAL_SALES FROM SALES_TXN WHERE TXN_DATE = CURRENT_DATE - 1 GROUP BY CUSTOMER_ID HAVING SUM(AMOUNT) > 1000;
- C
SELECT CUSTOMER_ID, AMOUNT AS TOTAL_SALES FROM SALES_TXN WHERE TXN_DATE = CURRENT_DATE - 1 GROUP BY CUSTOMER_ID HAVING AMOUNT > 1000;
- D
SELECT CUSTOMER_ID, SUM(AMOUNT) AS TOTAL_SALES FROM SALES_TXN GROUP BY CUSTOMER_ID HAVING TXN_DATE = CURRENT_DATE - 1 AND SUM(AMOUNT) > 1000;
Show answer and explanation
Correct answer: B
Explanation
This question tests practical understanding of SQL query processing in Snowflake: WHERE filters source rows before aggregation, GROUP BY forms groups, and HAVING filters aggregated results. For this scenario, the previous-day condition belongs in WHERE because it limits which transaction rows are included in the totals. The threshold condition on the total sales belongs in HAVING because it depends on SUM(AMOUNT), which is computed after grouping. This is consistent with standard Snowflake SQL behavior and documented SQL best practices for filtering grouped data.
- A. Incorrect.
Incorrect. Aggregate functions such as SUM(AMOUNT) cannot be used in the WHERE clause. In Snowflake SQL, WHERE filters rows before grouping and aggregation occur. To filter grouped results based on an aggregate value, HAVING must be used after GROUP BY.
- B. Correct.
Correct. This statement first filters rows to only the previous day's transactions in the WHERE clause, then groups those rows by CUSTOMER_ID, calculates SUM(AMOUNT), and finally uses HAVING to keep only customers whose aggregated total exceeds 1000. This matches the reporting requirement exactly.
- C. Incorrect.
Incorrect. Because the report requires total sales per customer, the query must aggregate AMOUNT using SUM(AMOUNT). Selecting AMOUNT directly while grouping only by CUSTOMER_ID is invalid unless AMOUNT is also grouped or aggregated. In addition, HAVING AMOUNT > 1000 would test individual values, not the customer's total.
- D. Incorrect.
Incorrect. TXN_DATE is not grouped or aggregated, so referencing it in HAVING in this way is not valid for this requirement. More importantly, filtering to the previous day's rows should happen before aggregation in the WHERE clause so that only relevant transactions are included in each customer's total.