DAA-C01 Question 152
Single answerPerform pre-math calculations (e.g., randomization, ranking, grouping, min/max)A retail analytics team is building a Snowflake worksheet to feed a dashboard that highlights the top 3 products by daily revenue within each region. The source table SALES_FACT contains REGION, PRODUCT_ID, SALE_DATE, and REVENUE. The team wants the result to return only the highest-performing 3 products for each REGION and SALE_DATE combination, while preserving ties correctly based on total daily revenue per product. Which SQL approach best meets this requirement?
- A
Aggregate revenue by REGION, SALE_DATE, and PRODUCT_ID, then use RANK() OVER (PARTITION BY REGION, SALE_DATE ORDER BY SUM(REVENUE) DESC) in a subquery or CTE, and filter rows where rank <= 3.
- B
Use ROW_NUMBER() OVER (ORDER BY REVENUE DESC) directly on SALES_FACT and filter rows where row_number <= 3.
- C
Use NTILE(3) OVER (PARTITION BY REGION ORDER BY REVENUE DESC) and keep the first tile for each region and date.
- D
Aggregate revenue by PRODUCT_ID only, then use DENSE_RANK() OVER (ORDER BY MAX(REVENUE) DESC) and filter rows where dense_rank <= 3.
Show answer and explanation
Correct answer: A
Explanation
This scenario tests pre-math calculations in Snowflake using grouping and ranking before a final analytical result is produced. The key is to align the calculation grain with the business question: first aggregate REVENUE by REGION, SALE_DATE, and PRODUCT_ID, then rank products within each REGION and SALE_DATE partition. For preserving ties in top-N reporting, RANK() or DENSE_RANK() are appropriate depending on the desired rank behavior; here, RANK() satisfies the requirement to preserve ties correctly in ranked positions. ROW_NUMBER() is not suitable when tied values should not be arbitrarily broken. NTILE() is for bucketization, not top-N selection. Snowflake documentation on window functions, ranking functions, GROUP BY, and QUALIFY supports this pattern as a best practice for analytic SQL workflows.
- A. Correct.
Correct. The requirement is to identify the top 3 products by total daily revenue within each REGION and SALE_DATE. That means the data must first be grouped at the correct grain: REGION, SALE_DATE, PRODUCT_ID. After aggregation, a window ranking function should be applied partitioned by REGION and SALE_DATE. Using RANK() ordered by aggregated revenue descending preserves ties correctly because products with the same total revenue receive the same rank. Filtering for rank <= 3 returns all products whose rank falls in the top 3 positions, including ties. In Snowflake, this is commonly implemented with a subquery/CTE or with QUALIFY when appropriate.
- B. Incorrect.
Incorrect. ROW_NUMBER() assigns a unique sequence to each row and does not preserve ties. It also operates here on the raw SALES_FACT rows rather than on revenue aggregated by product, date, and region, which does not match the business requirement. This option reflects a common mistake: ranking transaction rows instead of grouped product totals.
- C. Incorrect.
Incorrect. NTILE(3) splits rows into 3 buckets of approximately equal size; it does not return the top 3 ranked products. It is used for bucketing/distribution analysis, not precise top-N logic. In addition, this option partitions only by REGION and ignores SALE_DATE, so results would mix different days together.
- D. Incorrect.
Incorrect. Aggregating by PRODUCT_ID only ignores the required regional and daily grouping. Using MAX(REVENUE) is also wrong because the requirement is based on total daily revenue, not the maximum individual transaction revenue. Although DENSE_RANK() can be useful for ranking, this option applies it at the wrong level of aggregation and partitioning.