DAA-C01 Question 199
Single answer3.2 Perform descriptive analyses.A retail analytics team stores daily sales in a Snowflake table SALES_FACT with the columns STORE_ID, SALE_DATE, NET_SALES, and CHANNEL. Leadership wants a descriptive analysis that shows the typical daily sales level for each store over the last 90 days, while minimizing the impact of occasional extreme promotion days. The team also wants a measure of spread to compare variability between stores. Which query pattern best meets this requirement?
- A
SELECT STORE_ID, AVG(NET_SALES) AS TYPICAL_DAILY_SALES, STDDEV(NET_SALES) AS SALES_VARIABILITY FROM SALES_FACT WHERE SALE_DATE >= DATEADD(day, -90, CURRENT_DATE()) GROUP BY STORE_ID;
- B
SELECT STORE_ID, MEDIAN(NET_SALES) AS TYPICAL_DAILY_SALES, STDDEV(NET_SALES) AS SALES_VARIABILITY FROM SALES_FACT WHERE SALE_DATE >= DATEADD(day, -90, CURRENT_DATE()) GROUP BY STORE_ID;
- C
SELECT STORE_ID, MODE(NET_SALES) AS TYPICAL_DAILY_SALES, VARIANCE(NET_SALES) AS SALES_VARIABILITY FROM SALES_FACT WHERE SALE_DATE >= DATEADD(day, -90, CURRENT_DATE()) GROUP BY STORE_ID;
- D
SELECT STORE_ID, SUM(NET_SALES) / COUNT(DISTINCT SALE_DATE) AS TYPICAL_DAILY_SALES, RANGE(NET_SALES) AS SALES_VARIABILITY FROM SALES_FACT WHERE SALE_DATE >= DATEADD(day, -90, CURRENT_DATE()) GROUP BY STORE_ID;
Show answer and explanation
Correct answer: B
Explanation
For descriptive analysis in Snowflake, choosing the right measure of central tendency depends on the data distribution and business question. When occasional extreme values are present, MEDIAN is preferred over AVG because it is more robust to outliers. For spread, STDDEV is a standard descriptive statistic supported by Snowflake and is commonly used to compare variability across groups. MODE is rarely useful for continuous financial measures such as daily sales, and simple averages or total-per-day calculations remain influenced by extreme values. Snowflake supports aggregate statistical functions such as MEDIAN, STDDEV, and VARIANCE for grouped descriptive analysis, which are appropriate tools for this kind of store-level summary.
- A. Incorrect.
This is incorrect because AVG(NET_SALES) is sensitive to outliers such as extreme promotion days. Although STDDEV(NET_SALES) is a valid spread measure in Snowflake, the requirement specifically asks for a typical daily sales metric that minimizes the impact of occasional extreme values. Using the mean does not best satisfy that requirement.
- B. Correct.
This is correct. MEDIAN(NET_SALES) is a robust descriptive statistic for central tendency and is less affected by extreme values than AVG. STDDEV(NET_SALES) provides a standard measure of dispersion that allows comparison of variability across stores. This combination aligns well with the stated business need for a typical daily level plus a measure of spread over the last 90 days.
- C. Incorrect.
This is incorrect because MODE(NET_SALES) is generally not an appropriate summary for continuous or highly variable numeric sales amounts; many stores may have no repeated NET_SALES values, making the mode uninformative. VARIANCE is a valid spread metric, but the central tendency choice does not realistically meet the requirement for typical daily sales in this scenario.
- D. Incorrect.
This is incorrect for two reasons. First, SUM(NET_SALES) / COUNT(DISTINCT SALE_DATE) is effectively an average per active day, so it remains sensitive to extreme promotion days and does not reduce outlier influence like MEDIAN does. Second, RANGE(NET_SALES) is not a Snowflake aggregate function for this purpose, and even conceptually range is highly sensitive to outliers and is not the best variability metric here.