DAA-C01 Question 260
Single answerIdentify patterns and trendsA retail analytics team stores daily sales in a Snowflake table SALES_DAILY with columns STORE_ID, SALES_DATE, and NET_SALES. The team wants to identify stores whose sales are trending downward over the most recent 30 days, while minimizing the impact of day-to-day volatility such as weekends and promotions. They need a SQL approach that can be operationalized in Snowflake and used in a dashboard refresh each morning. Which approach is the MOST appropriate?
- A
For each store, calculate a 7-day moving average of NET_SALES ordered by SALES_DATE, then compare the moving average at the end of the 30-day period to the moving average near the beginning of the period or evaluate the slope of the smoothed series.
- B
For each store, compute AVG(NET_SALES) over the 30-day period and flag stores with averages below the global company average as downward-trending.
- C
For each store, sort the 30 daily NET_SALES values from lowest to highest and compare the bottom 15 values to the top 15 values to determine whether sales are declining.
- D
For each store, calculate the cumulative sum of NET_SALES over the 30-day period and flag stores with the smallest cumulative totals as downward-trending.
Show answer and explanation
Correct answer: A
Explanation
The key requirement is to identify patterns and trends in a time-ordered series while reducing noise from short-term fluctuations. In Snowflake, window functions are the most practical SQL-native method for this type of analysis. A moving average preserves date order and smooths volatility, making it easier to detect underlying declines. This aligns with common analytics best practices for trend analysis in time-series data. Approaches based only on average level, sorted values, or cumulative totals do not measure trend because they either ignore time order or conflate size with direction. Relevant Snowflake documentation includes the use of window functions and analytic functions with ORDER BY and window frames to calculate rolling metrics for time-series analysis.
- A. Correct.
Correct. A moving average is a standard time-series smoothing technique for identifying underlying patterns and trends when daily values are noisy. In Snowflake, this can be implemented with window functions such as AVG(NET_SALES) OVER (PARTITION BY STORE_ID ORDER BY SALES_DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Comparing smoothed values across time, or deriving a slope from the smoothed series, preserves temporal order and is appropriate for identifying downward trends.
- B. Incorrect.
Incorrect. A 30-day average measures overall level, not direction over time. A store can have a high or low average without trending downward. Comparing a store's average to the company's average identifies relative performance, not whether the store's sales are declining during the period. This is a common mistake when analysts confuse magnitude with trend.
- C. Incorrect.
Incorrect. Sorting the daily values destroys the time sequence, which is essential for trend analysis. Once the observations are reordered by value instead of date, the result says nothing about whether sales decreased over time. This distractor reflects a misunderstanding between distribution analysis and time-series trend detection.
- D. Incorrect.
Incorrect. Cumulative totals increase over time by design and are useful for tracking aggregate progress, not for identifying whether daily sales are trending downward. Stores with smaller cumulative totals may simply be smaller stores, not stores with declining sales. This approach confuses total volume with directional trend.