SnowPro Associate: Platform Question 301
Single answer○ Use of SELECT statementsA data analyst needs to produce a daily report from the SALES table showing one row per customer for the current month. The report must include the customer's total sales amount and only include customers whose total sales for the month exceed 10,000. Which SELECT statement correctly returns the required result?
- A
SELECT customer_id, SUM(amount) AS total_sales FROM sales WHERE sale_date >= DATE_TRUNC('MONTH', CURRENT_DATE()) AND SUM(amount) > 10000 GROUP BY customer_id;
- B
SELECT customer_id, SUM(amount) AS total_sales FROM sales WHERE sale_date >= DATE_TRUNC('MONTH', CURRENT_DATE()) GROUP BY customer_id HAVING SUM(amount) > 10000;
- C
SELECT customer_id, amount AS total_sales FROM sales WHERE sale_date >= DATE_TRUNC('MONTH', CURRENT_DATE()) GROUP BY customer_id HAVING amount > 10000;
- D
SELECT customer_id, SUM(amount) AS total_sales FROM sales GROUP BY customer_id WHERE sale_date >= DATE_TRUNC('MONTH', CURRENT_DATE()) HAVING total_sales > 10000;
Show answer and explanation
Correct answer: B
Explanation
This question tests practical use of SELECT statements with WHERE, GROUP BY, aggregate functions, and HAVING in Snowflake. The correct pattern is: use WHERE to filter individual rows before aggregation, use GROUP BY to create one result row per grouping key, and use HAVING to filter aggregated groups. For this scenario, the analyst needs current-month rows only, grouped by customer_id, with SUM(amount) computed per customer and filtered to totals greater than 10,000. This aligns with Snowflake SQL behavior documented for SELECT, GROUP BY, and HAVING clauses.
- A. Incorrect.
Incorrect. Aggregate filters such as SUM(amount) > 10000 cannot be placed in the WHERE clause because WHERE is evaluated before grouping and aggregation. In Snowflake SQL, conditions on aggregated results must be placed in the HAVING clause.
- B. Correct.
Correct. This query first filters rows to the current month in the WHERE clause, then groups rows by customer_id, calculates SUM(amount) for each customer, and finally uses HAVING to keep only customers whose monthly total exceeds 10,000. This matches the required one-row-per-customer result.
- C. Incorrect.
Incorrect. The query selects amount without aggregating it, even though the requirement is to return the customer's total sales. Also, HAVING amount > 10000 applies to a non-aggregated column and does not correctly test the summed monthly sales per customer. This reflects a common mistake of confusing row-level filtering with group-level filtering.
- D. Incorrect.
Incorrect. The clause order is invalid. In a SELECT statement, WHERE must appear before GROUP BY, not after it. Also, although Snowflake supports using aliases in some clauses, the invalid statement structure makes this query incorrect.