DAA-C01 Question 219
Single answerUse statistics and built-in functionsA retail analytics team stores transaction amounts in a Snowflake table SALES_TXN with columns STORE_ID, TXN_TS, and AMOUNT. They need a daily monitoring query that flags stores whose transaction amounts are highly skewed by a few unusually large purchases. The analyst wants a robust summary statistic that is less sensitive to extreme outliers than the average, while still being computed directly in SQL for each STORE_ID and calendar day. Which approach best meets this requirement?
- A
Use MEDIAN(AMOUNT) grouped by STORE_ID and CAST(TXN_TS AS DATE) to summarize the typical transaction amount per store per day.
- B
Use AVG(AMOUNT) grouped by STORE_ID and CAST(TXN_TS AS DATE), because averages automatically reduce the impact of outliers when enough rows exist.
- C
Use STDDEV(AMOUNT) grouped by STORE_ID and CAST(TXN_TS AS DATE), because standard deviation returns the central value of the daily transactions.
- D
Use MODE(AMOUNT) grouped by STORE_ID and CAST(TXN_TS AS DATE), because the most frequent amount is always the best robust measure for continuous transaction data.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use MEDIAN because the scenario explicitly asks for a robust summary statistic that is less affected by extreme values than the average. In Snowflake, MEDIAN is available as a built-in aggregate and window function for numeric data, making it suitable for practical analytical SQL. By grouping on STORE_ID and the date derived from TXN_TS, the analyst can compute a daily per-store robust center. AVG is valid SQL but is not robust to outliers, while STDDEV measures spread rather than center. MODE can be useful in some categorical or low-cardinality contexts, but it is usually not the best choice for continuous transaction amounts. This aligns with standard analytical best practices and Snowflake documentation for statistical aggregate functions such as MEDIAN, AVG, STDDEV, and MODE.
- A. Correct.
Correct. MEDIAN is a built-in statistical aggregate in Snowflake and is much less sensitive to extreme high or low values than AVG. In a transaction dataset with occasional unusually large purchases, the median better represents the typical transaction amount for each STORE_ID and day. Grouping by STORE_ID and the date portion of TXN_TS matches the business requirement for daily per-store monitoring.
- B. Incorrect.
Incorrect. AVG is often the first metric analysts use, but it is sensitive to outliers. A few very large purchases can pull the mean upward and make the result less representative of the typical transaction amount. This option reflects a common misconception that a larger sample size alone makes averages robust to skewed distributions.
- C. Incorrect.
Incorrect. STDDEV measures dispersion, not central tendency. It can help quantify variability or identify unusual spread in transaction amounts, but it does not summarize the typical transaction amount. Choosing STDDEV here confuses measures of spread with measures of center.
- D. Incorrect.
Incorrect. MODE returns the most frequent value, which is often not useful for continuous or highly granular numeric transaction amounts because exact repeats may be rare or not meaningful. It also is not generally the preferred robust summary statistic for skewed continuous data. This option reflects a misunderstanding of when mode is appropriate.