SnowPro Associate: Platform Question 40
Single answer○ SQLA data engineer needs to create a daily sales summary table in Snowflake from a transaction table named SALES_TXN. The source table contains these columns: ORDER_TS (TIMESTAMP_NTZ), CUSTOMER_ID (NUMBER), REGION (VARCHAR), and AMOUNT (NUMBER). The business wants one row per calendar day and region, with the total sales amount for each group. Which SQL statement correctly produces the required result?
- A
SELECT ORDER_TS, REGION, SUM(AMOUNT) AS TOTAL_SALES FROM SALES_TXN GROUP BY ORDER_TS, REGION;
- B
SELECT DATE_TRUNC('DAY', ORDER_TS) AS SALES_DAY, REGION, SUM(AMOUNT) AS TOTAL_SALES FROM SALES_TXN GROUP BY DATE_TRUNC('DAY', ORDER_TS), REGION;
- C
SELECT CAST(ORDER_TS AS DATE) AS SALES_DAY, REGION, AMOUNT AS TOTAL_SALES FROM SALES_TXN GROUP BY SALES_DAY, REGION;
- D
SELECT REGION, SUM(AMOUNT) AS TOTAL_SALES FROM SALES_TXN GROUP BY CUSTOMER_ID, REGION;
Show answer and explanation
Correct answer: B
Explanation
To produce a daily summary in Snowflake SQL, the timestamp must first be normalized to the day level before aggregation. DATE_TRUNC('DAY', ORDER_TS) is a standard Snowflake function for truncating a timestamp to the specified date part. Once truncated, the query can GROUP BY the derived day and REGION, and calculate SUM(AMOUNT). Option 2 is therefore the only statement that satisfies both the grouping requirement and SQL aggregation rules. This aligns with Snowflake SQL best practices for time-based aggregation using functions such as DATE_TRUNC and aggregate functions like SUM.
- A. Incorrect.
Incorrect. GROUP BY ORDER_TS and REGION creates groups at the full timestamp value, not at the calendar day level. If multiple transactions occur on the same day at different times, they will be split into separate groups. This does not meet the requirement of one row per day and region.
- B. Correct.
Correct. DATE_TRUNC('DAY', ORDER_TS) converts each timestamp to the start of its calendar day, allowing all transactions from the same day and region to be aggregated together. SUM(AMOUNT) then correctly calculates total sales for each day-region combination.
- C. Incorrect.
Incorrect. Although CAST(ORDER_TS AS DATE) can derive the date portion, this query selects AMOUNT directly without aggregating it. In Snowflake SQL, any non-grouped selected column must be aggregated. This statement would fail because AMOUNT is neither grouped nor wrapped in an aggregate function.
- D. Incorrect.
Incorrect. This query groups by CUSTOMER_ID and REGION, not by day and region. It also omits any date derivation from ORDER_TS, so it cannot produce one row per calendar day and region. This reflects a common mistake of grouping by an unrelated dimension.