DAA-C01 Question 151
Single answerPerform pre-math calculations (e.g., randomization, ranking, grouping, min/max)A retail analytics team uses Snowsight to build a worksheet-based analysis on a SALES table with the columns REGION, SALES_REP_ID, ORDER_ID, and REVENUE. The business wants a visualization that shows only the top 3 sales representatives by total revenue within each region. Before creating the chart, the analyst needs to prepare the dataset in Snowflake so the visualization only includes those rows. Which approach should the analyst use?
- A
Aggregate revenue by REGION and SALES_REP_ID, calculate RANK() OVER (PARTITION BY REGION ORDER BY SUM(REVENUE) DESC), and filter to ranks 1 through 3.
- B
Use ROW_NUMBER() OVER (ORDER BY REVENUE DESC) on the base SALES table and filter to the first 3 rows overall, then build the chart by REGION.
- C
Use MIN(REVENUE) and MAX(REVENUE) by REGION to identify the top 3 sales representatives in each region.
- D
Group by REGION only, calculate SUM(REVENUE), and use ORDER BY SUM(REVENUE) DESC LIMIT 3 to return the top representatives per region.
Show answer and explanation
Correct answer: A
Explanation
The correct solution uses a grouped aggregation followed by a window ranking function partitioned by the business grouping dimension. In Snowflake, this is commonly implemented by first calculating total revenue per REGION and SALES_REP_ID, then applying RANK(), DENSE_RANK(), or ROW_NUMBER() OVER (PARTITION BY REGION ORDER BY total_revenue DESC), depending on how ties should be handled. For a top-N-per-group requirement, a partitioned window function is the appropriate pre-math calculation before building a visualization. Snowflake documentation for window functions and ranking functions describes how PARTITION BY creates independent ranking scopes, while GROUP BY produces the aggregated grain needed for representative-level analysis. Best practice is to prepare the dataset at the exact analytical grain required before sending it to a chart.
- A. Correct.
Correct. To find the top 3 sales representatives within each region, the analyst must first aggregate at the representative level using GROUP BY REGION, SALES_REP_ID and SUM(REVENUE). Then a window ranking function such as RANK() can be applied with PARTITION BY REGION and ORDER BY the aggregated revenue descending. Filtering to ranks 1, 2, and 3 returns the top performers per region. This is a standard pre-math preparation pattern in Snowflake for ranking within groups before visualization.
- B. Incorrect.
Incorrect. ROW_NUMBER() without PARTITION BY REGION ranks rows across the entire dataset, not within each region. It also operates on individual sales rows unless the data is first aggregated, so it would identify the top revenue transactions rather than the top representatives by total revenue. This is a common mistake when analysts confuse row-level ordering with grouped ranking.
- C. Incorrect.
Incorrect. MIN() and MAX() return only the minimum or maximum values in each group and do not identify the top 3 representatives. Even if used by region, these functions cannot produce a ranked list of multiple representatives. This distractor reflects a misunderstanding between summary statistics and ranking logic.
- D. Incorrect.
Incorrect. Grouping only by REGION collapses all representatives into one row per region, so the result no longer contains SALES_REP_ID and cannot identify top representatives. In addition, LIMIT 3 would return only three rows total, not the top three representatives within each region. This is a frequent error when users apply global limiting instead of partitioned ranking.