SnowPro Associate: Platform Question 74
Single answer○ SQLA data engineer needs to create a monthly sales summary table in Snowflake from a transactional table named SALES_TXN with the columns ORDER_ID, ORDER_TS, CUSTOMER_ID, and AMOUNT. The requirement is to produce one row per calendar month with the total sales amount for that month, and the result must be sorted from oldest month to newest month. Which SQL statement correctly meets this requirement?
- A
SELECT DATE_TRUNC('MONTH', ORDER_TS) AS SALES_MONTH, SUM(AMOUNT) AS TOTAL_SALES FROM SALES_TXN 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 SALES_TXN 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 SALES_TXN 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 SALES_TXN GROUP BY ORDER_TS ORDER BY SALES_MONTH;
Show answer and explanation
Correct answer: A
Explanation
For month-based aggregation in Snowflake, DATE_TRUNC is the best choice when the goal is to roll up data to a specific time grain such as month, quarter, or year. In this scenario, DATE_TRUNC('MONTH', ORDER_TS) preserves both the year and month, which avoids incorrectly combining data from the same month across different years. By contrast, EXTRACT or DATE_PART with only 'MONTH' returns a numeric month and can lead to incorrect aggregation unless paired with year. Snowflake documentation for DATE_TRUNC, DATE_PART, and EXTRACT describes these differences clearly. In practice, using DATE_TRUNC for time-series summaries is a common best practice because it produces a sortable date/timestamp value that works well in GROUP BY and ORDER BY clauses.
- A. Correct.
Correct. DATE_TRUNC('MONTH', ORDER_TS) returns the timestamp truncated to the first instant of the month, which is appropriate for grouping all rows within the same calendar month together. SUM(AMOUNT) correctly aggregates sales for each month, and ORDER BY SALES_MONTH sorts the results chronologically from oldest to newest. This is the standard Snowflake SQL approach for monthly time-based aggregation.
- B. Incorrect.
Incorrect. EXTRACT(MONTH FROM ORDER_TS) returns only the numeric month value (1-12), not the year. That means January 2024 and January 2025 would be grouped together, which does not satisfy the requirement for one row per calendar month across time. This is a common mistake when aggregating by month without considering the year.
- C. Incorrect.
Incorrect. TO_DATE(ORDER_TS) converts the timestamp to a date at the day level, so grouping by TO_DATE(ORDER_TS) would create one row per day, not one row per month. Although the aggregation and ordering syntax are valid, the granularity is too detailed for the stated requirement.
- D. Incorrect.
Incorrect. DATE_PART('MONTH', ORDER_TS) returns only the month number, which already loses the year context. In addition, grouping by ORDER_TS groups at the individual timestamp level rather than the month level, so the aggregation would not produce one row per month. This option combines two common SQL grouping errors.