DAA-C01 Question 220
Single answerUse statistics and built-in functionsA retail analytics team stores daily online order totals in a Snowflake table SALES_DAILY with columns ORDER_DATE and ORDER_AMOUNT. They need a query that flags unusually large orders for further review using a z-score approach. The analyst wants to compare each row's ORDER_AMOUNT to the overall distribution of ORDER_AMOUNT in the table and return only rows where the absolute z-score is greater than 3. Which query best meets this requirement in Snowflake?
- A
SELECT ORDER_DATE, ORDER_AMOUNT, ABS((ORDER_AMOUNT - AVG(ORDER_AMOUNT) OVER()) / STDDEV_SAMP(ORDER_AMOUNT) OVER()) AS Z_SCORE FROM SALES_DAILY QUALIFY Z_SCORE > 3;
- B
SELECT ORDER_DATE, ORDER_AMOUNT, ABS((ORDER_AMOUNT - MEDIAN(ORDER_AMOUNT) OVER()) / VAR_SAMP(ORDER_AMOUNT) OVER()) AS Z_SCORE FROM SALES_DAILY WHERE Z_SCORE > 3;
- C
SELECT ORDER_DATE, ORDER_AMOUNT, ABS((ORDER_AMOUNT - AVG(ORDER_AMOUNT)) / STDDEV_SAMP(ORDER_AMOUNT)) AS Z_SCORE FROM SALES_DAILY WHERE ABS(Z_SCORE) > 3 GROUP BY ORDER_DATE, ORDER_AMOUNT;
- D
SELECT ORDER_DATE, ORDER_AMOUNT FROM SALES_DAILY WHERE ABS((ORDER_AMOUNT - AVG(ORDER_AMOUNT)) / STDDEV_POP(ORDER_AMOUNT)) > 3;
Show answer and explanation
Correct answer: A
Explanation
The correct solution uses Snowflake window functions to calculate dataset-level statistics while still returning each row. For outlier detection with z-scores, the standard pattern is to subtract the mean and divide by the standard deviation. In Snowflake, AVG(...) OVER() and STDDEV_SAMP(...) OVER() are built-in analytic functions that make this straightforward. QUALIFY is a Snowflake-specific best practice for filtering rows based on window-function results, avoiding the need for subqueries. This is more appropriate than WHERE when the filter depends on analytic calculations. Snowflake documentation for window functions, QUALIFY, and statistical functions such as AVG, STDDEV_SAMP, and related built-in functions supports this approach. Distractors reflect common mistakes: confusing variance with standard deviation, attempting to use aggregate functions instead of window functions for row-level comparisons, and filtering analytic results in WHERE instead of QUALIFY.
- A. Correct.
Correct. A z-score is typically calculated as (value - mean) / standard deviation. In Snowflake, using AVG(...) OVER() and STDDEV_SAMP(...) OVER() computes the overall mean and sample standard deviation across all rows while retaining row-level detail. QUALIFY is the appropriate clause for filtering on results of window functions after they are computed. Applying ABS(...) and keeping only values greater than 3 correctly identifies extreme outliers on either side of the distribution.
- B. Incorrect.
Incorrect. This uses MEDIAN and VAR_SAMP, which do not produce a standard z-score. Variance is not the same as standard deviation, so dividing by VAR_SAMP would scale the result incorrectly. In addition, filtering on the alias Z_SCORE in the WHERE clause is not appropriate when the value is derived from window functions; QUALIFY should be used instead.
- C. Incorrect.
Incorrect. AVG(ORDER_AMOUNT) and STDDEV_SAMP(ORDER_AMOUNT) without OVER() are aggregate functions, so this query attempts to mix aggregates and row-level columns in a way that does not produce the intended row-by-row z-score calculation. Grouping by ORDER_DATE and ORDER_AMOUNT also changes the granularity and does not compute the overall distribution for each individual row. The alias Z_SCORE also cannot be reliably filtered this way in WHERE in Snowflake for this pattern.
- D. Incorrect.
Incorrect. This query uses aggregate functions in the WHERE clause without grouping or windowing, which is invalid for the stated row-level outlier detection requirement. It also does not preserve row-level comparison to the overall distribution in a valid Snowflake pattern. While population standard deviation can be appropriate in some analytical contexts, the main issue here is incorrect SQL structure for computing and filtering per-row z-scores.