DAA-C01 Question 216
Single answerAnalyze statistics and trendsA retail analytics team stores daily sales in a Snowflake table with columns ORDER_DATE, REGION, and SALES_AMOUNT. The team wants a report that shows each day's sales, a 7-day moving average of sales within each region, and the day-over-day sales change within each region. The analyst must ensure the calculations are accurate even when multiple regions have data for the same date. Which SQL approach best meets the requirement?
- A
Use SUM(SALES_AMOUNT) OVER (PARTITION BY REGION ORDER BY ORDER_DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) / 7 for the moving average, and SALES_AMOUNT - LAG(SALES_AMOUNT) OVER (PARTITION BY REGION ORDER BY ORDER_DATE) for day-over-day change.
- B
Use AVG(SALES_AMOUNT) OVER (ORDER BY ORDER_DATE RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) for the moving average, and SALES_AMOUNT - LAG(SALES_AMOUNT) OVER (ORDER BY ORDER_DATE) for day-over-day change.
- C
Use AVG(SALES_AMOUNT) OVER (PARTITION BY REGION ORDER BY ORDER_DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for the moving average, and SALES_AMOUNT - LAG(SALES_AMOUNT) OVER (PARTITION BY REGION ORDER BY ORDER_DATE) for day-over-day change.
- D
Use AVG(SALES_AMOUNT) GROUP BY REGION, ORDER_DATE for the moving average, and a self-join on ORDER_DATE - 1 for day-over-day change.
Show answer and explanation
Correct answer: C
Explanation
For time-series analysis in Snowflake, analytic window functions are the preferred method for calculating rolling statistics and trend comparisons such as moving averages and period-over-period deltas. The key requirement in this scenario is that calculations must be isolated within each REGION, which makes PARTITION BY REGION essential. ORDER BY ORDER_DATE defines the sequence of rows in each partition. Using AVG(...) with a ROWS BETWEEN 6 PRECEDING AND CURRENT ROW frame is the standard way to produce a 7-row moving average because it automatically adjusts for partial windows at the beginning of the series. LAG(...) is the appropriate function for day-over-day comparisons because it returns the prior row's value within the same partition. This aligns with Snowflake documentation and best practices for window functions, including AVG and LAG, when analyzing statistics and trends across ordered data.
- A. Incorrect.
Incorrect. The LAG calculation is correctly partitioned by REGION and ordered by ORDER_DATE, so the day-over-day difference is scoped properly within each region. However, the moving average is implemented as SUM(...)/7 using a ROWS frame, which can produce inaccurate results when there are fewer than 7 preceding rows, such as at the start of a region's time series. In those cases Snowflake would still divide by 7 instead of the actual row count in the frame, understating the average. AVG(...) over the frame is the safer and standard approach.
- B. Incorrect.
Incorrect. This option does not partition by REGION, so both the moving average and the LAG comparison can blend data from different regions that share the same dates. That violates the requirement to calculate trends within each region. In addition, RANGE frames are value-based rather than row-based and can behave differently when duplicate ORDER_DATE values exist or when the grain is not strictly one row per date per partition.
- C. Correct.
Correct. This approach correctly uses window functions partitioned by REGION so each region's trend is calculated independently. AVG(SALES_AMOUNT) OVER (PARTITION BY REGION ORDER BY ORDER_DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) produces a rolling 7-row average within each region, and LAG(SALES_AMOUNT) OVER (PARTITION BY REGION ORDER BY ORDER_DATE) correctly references the previous day's value within the same region. This is the most appropriate pattern for analyzing trends and statistics over time in Snowflake.
- D. Incorrect.
Incorrect. GROUP BY REGION, ORDER_DATE can aggregate daily sales, but by itself it does not create a rolling 7-day moving average. A self-join on ORDER_DATE - 1 is also a more brittle and less scalable pattern than using LAG for day-over-day comparisons. It may fail or require extra logic if dates are missing, and it does not align with Snowflake best practices for time-series trend analysis, where analytic window functions are preferred.