DAA-C01 Question 200
Single answer3.2 Perform descriptive analyses.A retail analytics team stores daily order data in a Snowflake table with columns ORDER_ID, CUSTOMER_ID, ORDER_DATE, REGION, and ORDER_AMOUNT. They need a descriptive analysis query that helps identify the typical order value and the impact of unusually large orders for each REGION over the last 90 days. The analyst wants a result that includes a measure of central tendency, a measure that is resistant to outliers, and a direct comparison between the average and the middle value of the distribution. Which query best meets this requirement?
- A
SELECT REGION, AVG(ORDER_AMOUNT) AS AVG_ORDER, MEDIAN(ORDER_AMOUNT) AS MEDIAN_ORDER, STDDEV(ORDER_AMOUNT) AS STD_ORDER FROM ORDERS WHERE ORDER_DATE >= DATEADD(day, -90, CURRENT_DATE()) GROUP BY REGION;
- B
SELECT REGION, SUM(ORDER_AMOUNT) AS TOTAL_ORDER, COUNT(*) AS ORDER_COUNT, MODE(ORDER_AMOUNT) AS TYPICAL_ORDER FROM ORDERS WHERE ORDER_DATE >= DATEADD(day, -90, CURRENT_DATE()) GROUP BY REGION;
- C
SELECT REGION, MIN(ORDER_AMOUNT) AS MIN_ORDER, MAX(ORDER_AMOUNT) AS MAX_ORDER, RANGE(ORDER_AMOUNT) AS ORDER_RANGE FROM ORDERS WHERE ORDER_DATE >= DATEADD(day, -90, CURRENT_DATE()) GROUP BY REGION;
- D
SELECT REGION, AVG(ORDER_AMOUNT) AS AVG_ORDER, PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY ORDER_AMOUNT) AS P90_ORDER, VARIANCE(ORDER_AMOUNT) AS VAR_ORDER FROM ORDERS WHERE ORDER_DATE >= DATEADD(day, -90, CURRENT_DATE()) GROUP BY REGION;
Show answer and explanation
Correct answer: A
Explanation
For descriptive analysis in Snowflake, common aggregate functions such as AVG, MEDIAN, STDDEV, COUNT, MIN, and MAX are used to summarize distributions. In this scenario, the business explicitly wants to understand both the typical order value and the effect of unusually large orders. The best way to do that is to compare AVG and MEDIAN by REGION. AVG can be pulled upward by a small number of very large orders, while MEDIAN is resistant to outliers and better represents the middle of the distribution. A noticeable gap between AVG and MEDIAN often indicates skew. Snowflake supports MEDIAN as an aggregate function, making option 1 the best fit. This aligns with best practices for descriptive analytics: choose summary statistics that reflect both central tendency and robustness when distributions may be skewed. See Snowflake documentation for aggregate functions such as AVG, MEDIAN, and STDDEV, as well as date filtering with DATEADD.
- A. Correct.
Correct. This query directly supports descriptive analysis of order values by REGION for the last 90 days. AVG provides a standard measure of central tendency, MEDIAN provides a robust middle value that is less affected by extreme outliers, and comparing AVG to MEDIAN helps reveal skew caused by unusually large orders. STDDEV adds useful context about spread, although the key requirement is satisfied by the inclusion of both average and median.
- B. Incorrect.
Incorrect. SUM and COUNT are useful descriptive measures for total volume and activity, but they do not directly compare the mean to the middle of the distribution. MODE is also less useful for continuous monetary values like ORDER_AMOUNT, where repeated exact values may be uncommon or not meaningful as a representation of a typical order.
- C. Incorrect.
Incorrect. MIN and MAX describe extremes, and range can be conceptually useful for spread, but this option does not provide a central tendency measure or an outlier-resistant statistic such as median. Also, Snowflake does not provide a built-in RANGE aggregate function for numeric columns in this form, making this option factually incorrect as written.
- D. Incorrect.
Incorrect. AVG is a valid central tendency measure, and PERCENTILE_CONT(0.9) can help understand the upper end of the distribution. However, the requirement specifically calls for a direct comparison between the average and the middle value of the distribution. The 90th percentile is not the middle value; the median is. VARIANCE measures spread, not outlier-resistant central tendency.