DAA-C01 Question 261
Single answerIdentify patterns and trendsA retail analytics team stores daily sales in a Snowflake table SALES_DAILY with the columns STORE_ID, SALES_DATE, and NET_SALES. The team wants to identify 7-day sales trends for each store and compare each day's sales to that store's prior 7-day moving average. The solution must be implemented directly in SQL so it can be reused in dashboards and scheduled reports. Which SQL approach best meets this requirement?
- A
Use window functions to calculate AVG(NET_SALES) OVER (PARTITION BY STORE_ID ORDER BY SALES_DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), and use LAG on that moving average to compare the current day's sales to the prior day's 7-day average.
- B
Use a GROUP BY STORE_ID, SALES_DATE query with AVG(NET_SALES), because grouping by date automatically includes the prior 7 days for each store when dates are consecutive.
- C
Use the MEDIAN aggregate over the entire SALES_DAILY table without PARTITION BY, because a global median is more efficient for identifying store-level trends than a moving average.
- D
Use a recursive CTE to iterate row by row through each store's sales history, because Snowflake does not support analytic window frames for moving averages.
Show answer and explanation
Correct answer: A
Explanation
For identifying patterns and trends such as rolling averages, Snowflake best practice is to use window functions with PARTITION BY and ORDER BY. In this scenario, partitioning by STORE_ID isolates each store's time series, and ordering by SALES_DATE enables trend calculations in sequence. A 7-day moving average is commonly expressed with a window frame like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW when the data is already at one row per day per store. If the business specifically needs the current day's sales compared with the prior day's 7-day moving average, a common pattern is to compute the moving average in a CTE and then use LAG on that derived value. This approach is aligned with Snowflake SQL analytic function capabilities documented for window functions such as AVG and LAG. GROUP BY alone does not provide rolling context, and recursive row-by-row logic is unnecessary because Snowflake natively supports windowed analytics for trend detection.
- A. Correct.
Correct. Window functions are the standard Snowflake SQL approach for identifying trends over ordered data. AVG(NET_SALES) OVER (PARTITION BY STORE_ID ORDER BY SALES_DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) calculates a 7-row moving average per store based on date order. To compare the current day's sales to the prior 7-day moving average, the moving average result can then be wrapped in a subquery or CTE and compared using LAG or by shifting the frame logic appropriately. This directly supports reusable SQL for BI tools and reporting workloads.
- B. Incorrect.
Incorrect. GROUP BY STORE_ID, SALES_DATE only aggregates rows at the store-date grain. It does not inherently look back across prior dates. A common misconception is that grouping by date can somehow imply a rolling period, but rolling calculations require window functions or self-joins with explicit date logic.
- C. Incorrect.
Incorrect. MEDIAN can be useful for robust distribution analysis, but it does not solve the stated requirement. The team needs a store-specific 7-day trend and a comparison to a prior rolling average. A global median across all stores ignores store-level partitioning and time-based sequencing, so it would not identify daily trends correctly.
- D. Incorrect.
Incorrect. Snowflake does support analytic window functions, including ORDER BY with window frames such as ROWS BETWEEN n PRECEDING AND CURRENT ROW. A recursive CTE would be unnecessarily complex, less readable, and generally not the best practice for rolling trend analysis in Snowflake.