SnowPro Associate: Platform Question 76
Single answer○ SQLA data engineer needs to create a monthly sales summary table in Snowflake from an ORDERS table with the columns ORDER_ID, ORDER_TS, CUSTOMER_ID, and AMOUNT. The business wants one row per month with the total sales amount for that month, and the output should be sorted from the earliest month to the latest month. Which SQL statement correctly produces the required result?
- A
SELECT DATE_TRUNC('MONTH', ORDER_TS) AS SALES_MONTH, SUM(AMOUNT) AS TOTAL_SALES FROM ORDERS GROUP BY DATE_TRUNC('MONTH', ORDER_TS) ORDER BY SALES_MONTH;
- B
SELECT EXTRACT(MONTH FROM ORDER_TS) AS SALES_MONTH, SUM(AMOUNT) AS TOTAL_SALES FROM ORDERS GROUP BY EXTRACT(MONTH FROM ORDER_TS) ORDER BY SALES_MONTH;
- C
SELECT TO_DATE(ORDER_TS) AS SALES_MONTH, SUM(AMOUNT) AS TOTAL_SALES FROM ORDERS GROUP BY TO_DATE(ORDER_TS) ORDER BY SALES_MONTH;
- D
SELECT DATE_PART('MONTH', ORDER_TS) AS SALES_MONTH, SUM(AMOUNT) AS TOTAL_SALES FROM ORDERS GROUP BY ORDER_TS ORDER BY SALES_MONTH;
Show answer and explanation
Correct answer: A
Explanation
In Snowflake SQL, DATE_TRUNC is the appropriate function when data must be aggregated at a specific time grain such as month, day, or year. For monthly summaries, DATE_TRUNC('MONTH',
- A. Correct.
Correct. DATE_TRUNC('MONTH', ORDER_TS) returns the timestamp truncated to the first moment of the month, which is appropriate for grouping all rows in the same month together. Using SUM(AMOUNT) aggregates the monthly sales, and ordering by the derived SALES_MONTH value sorts the results chronologically from earliest to latest. This is the standard Snowflake SQL approach for month-level aggregation when the full month and year context must be preserved.
- B. Incorrect.
Incorrect. EXTRACT(MONTH FROM ORDER_TS) returns only the numeric month (1 through 12) and does not include the year. This would incorrectly combine January 2023, January 2024, and any other January values into a single group. This is a common mistake when summarizing time-series data across multiple years.
- C. Incorrect.
Incorrect. TO_DATE(ORDER_TS) converts the timestamp to a date at the day level, not the month level. Grouping by TO_DATE(ORDER_TS) would produce one row per day rather than one row per month. Although the query would run, it does not satisfy the business requirement for monthly aggregation.
- D. Incorrect.
Incorrect. DATE_PART('MONTH', ORDER_TS) returns the month number only, similar to EXTRACT(MONTH ...), so it loses the year component. In addition, the query groups by ORDER_TS instead of the derived month expression, which would effectively create a separate group for each distinct timestamp value rather than one group per month.