SnowPro Associate: Platform Question 75
Single answer○ SQLA data engineer needs to produce a monthly sales summary in Snowflake. The source table SALES contains one row per transaction with the columns ORDER_ID, CUSTOMER_ID, ORDER_TS, REGION, and AMOUNT. The engineer wants a query that returns one row per REGION and calendar month, including only groups where the total sales amount is greater than 100,000. Which SQL statement correctly meets this requirement?
- A
SELECT REGION, DATE_TRUNC('MONTH', ORDER_TS) AS SALES_MONTH, SUM(AMOUNT) AS TOTAL_SALES FROM SALES WHERE SUM(AMOUNT) > 100000 GROUP BY REGION, DATE_TRUNC('MONTH', ORDER_TS);
- B
SELECT REGION, DATE_TRUNC('MONTH', ORDER_TS) AS SALES_MONTH, SUM(AMOUNT) AS TOTAL_SALES FROM SALES GROUP BY REGION, DATE_TRUNC('MONTH', ORDER_TS) HAVING SUM(AMOUNT) > 100000;
- C
SELECT REGION, MONTH(ORDER_TS) AS SALES_MONTH, SUM(AMOUNT) AS TOTAL_SALES FROM SALES GROUP BY REGION HAVING SUM(AMOUNT) > 100000;
- D
SELECT REGION, DATE_TRUNC('MONTH', ORDER_TS) AS SALES_MONTH, SUM(AMOUNT) AS TOTAL_SALES FROM SALES HAVING SUM(AMOUNT) > 100000 GROUP BY REGION, SALES_MONTH;
Show answer and explanation
Correct answer: B
Explanation
The correct answer is the query that uses GROUP BY for REGION and DATE_TRUNC('MONTH', ORDER_TS), and HAVING SUM(AMOUNT) > 100000 to filter aggregated groups. In Snowflake, DATE_TRUNC is commonly used to roll timestamps up to a calendar period such as month. The HAVING clause is specifically intended to filter grouped or aggregated results, whereas WHERE filters rows before aggregation. This distinction is fundamental SQL knowledge tested on the SnowPro Associate exam. Snowflake documentation for SELECT, GROUP BY, HAVING, and DATE_TRUNC aligns with this pattern and reflects standard best practice for building monthly summary queries.
- A. Incorrect.
Incorrect. SUM(AMOUNT) is an aggregate function, and aggregate filtering must be done in the HAVING clause, not the WHERE clause. WHERE filters individual rows before grouping, while HAVING filters grouped results after aggregation. This is a common SQL mistake when working with summary queries in Snowflake.
- B. Correct.
Correct. This query groups rows by REGION and the month derived from ORDER_TS using DATE_TRUNC('MONTH', ORDER_TS), calculates SUM(AMOUNT) for each group, and then uses HAVING to keep only groups whose total exceeds 100,000. This is the proper SQL pattern in Snowflake for filtering aggregated results.
- C. Incorrect.
Incorrect. This query does not group by month correctly because it groups only by REGION. Also, using MONTH(ORDER_TS) alone returns the numeric month and would combine the same month across different years, which is usually not appropriate for a calendar month summary unless the year is also included. It also would be invalid in Snowflake because SALES_MONTH is selected but not properly grouped in this form.
- D. Incorrect.
Incorrect. In Snowflake SQL, HAVING must come after GROUP BY, not before it. Also, while Snowflake allows aliases in some clauses, the clause order shown here is syntactically invalid. Someone might choose this option if they understand HAVING conceptually but overlook the required SQL statement structure.