DAA-C01 Question 215
Single answerAnalyze statistics and trendsA retail analytics team stores daily sales in a Snowflake table with columns STORE_ID, SALES_DATE, and NET_SALES. The team needs a report that, for each store, shows the current day's sales, a 7-day moving average of sales, and the day-over-day change compared with the previous day. The solution must be implemented in SQL and should scale without self-joining the table multiple times. Which approach best meets these requirements?
- A
Use window functions such as AVG(NET_SALES) OVER (PARTITION BY STORE_ID ORDER BY SALES_DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) and LAG(NET_SALES) OVER (PARTITION BY STORE_ID ORDER BY SALES_DATE).
- B
Use a PIVOT on SALES_DATE to turn the last 7 days into columns, then compute the moving average and previous-day change from those pivoted columns.
- C
Use APPROX_COUNT_DISTINCT over STORE_ID and SALES_DATE to estimate the rolling average and trend change efficiently.
- D
Use a GROUP BY STORE_ID, SALES_DATE with SUM(NET_SALES), then join the aggregated result to itself 7 times to calculate the moving average and once more to calculate the previous day's value.
Show answer and explanation
Correct answer: A
Explanation
For analyzing statistics and trends in Snowflake, window functions are the standard best-practice approach when you need rolling aggregates, ranking, and period-over-period comparisons within partitions such as STORE_ID. In this scenario, AVG over a defined window frame computes the 7-day moving average, and LAG retrieves the previous day's sales for day-over-day change calculations. This pattern avoids unnecessary self-joins and is aligned with Snowflake SQL analytic capabilities documented for window functions, including PARTITION BY, ORDER BY, frame clauses, and ranking/value functions like LAG. A practical implementation would often also calculate day-over-day change as NET_SALES - LAG(NET_SALES) OVER (...), or as a percentage change using a CASE expression to handle division by zero or NULL for the first row in each partition.
- A. Correct.
Correct. Snowflake window functions are the appropriate tool for trend and time-series analysis such as moving averages and period-over-period comparisons. AVG(...) OVER with PARTITION BY STORE_ID and ORDER BY SALES_DATE computes a rolling metric per store, and the frame ROWS BETWEEN 6 PRECEDING AND CURRENT ROW gives a 7-row moving window. LAG(...) returns the prior row's value in the same store partition, which is ideal for day-over-day analysis. This approach is concise, scalable, and avoids repeated self-joins.
- B. Incorrect.
Incorrect. PIVOT can reshape data, but it is not the best fit for rolling trend analysis. It requires hard-coding dates or generating dynamic SQL for changing date ranges, making it less maintainable. It also does not naturally support ongoing time-series calculations across arbitrary dates and stores as efficiently as window functions.
- C. Incorrect.
Incorrect. APPROX_COUNT_DISTINCT is used for approximate cardinality estimation, not for calculating averages or changes in numeric measures over time. Choosing this option reflects a misconception that approximate aggregate functions can be used interchangeably for trend metrics. They cannot produce a 7-day moving average or prior-day comparison of NET_SALES.
- D. Incorrect.
Incorrect. While self-joins can theoretically produce the needed result, this approach directly conflicts with the requirement to scale without joining the table multiple times. Repeated self-joins are more complex, harder to maintain, and generally less efficient than native analytic window functions for this use case.