Apply Analytic and Window Functions SNOWPRO-ADVANCED-DATA-ANALYST Practice Questions (Page 5)

Questions 21–25

1. 21application · medium

   A sales analyst needs to assign a unique sequential number to each order for a given customer, ordered by order date (oldest first). If two orders for the same customer share the same order date, the analyst still requires each order to receive a distinct number. Which function should be used in the OVER clause?

   • ADENSE_RANK() OVER (PARTITION BY customer_id ORDER BY order_date)
   • BRANK() OVER (PARTITION BY customer_id ORDER BY order_date)
   • CCOUNT(*) OVER (PARTITION BY customer_id ORDER BY order_date)
   • DROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date)
   Reveal Solution Discussion Select an answer first
2. 22foundation · easy

   Which ranking function always returns a unique sequential number for each row, even when values are tied?

   • ARANK()
   • BDENSE_RANK()
   • CROW_NUMBER()
   • DCUME_DIST()
   Reveal Solution Discussion Select an answer first
4. 23expert · medium

   A sales analyst needs to rank products by revenue within each category, but only wants to include products with revenue greater than $1000. The analyst writes: SELECT product_id, category_id, revenue, RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS rnk FROM products WHERE revenue > 1000. What is the result?

   • AThe ranking includes all products, but only shows those with revenue > 1000.
   • BThe query will fail because WHERE cannot be used with window functions.
   • CThe ranking will be NULL for products with revenue <= 1000.
   • DThe ranking is correctly computed only for products with revenue > 1000.
   Reveal Solution Discussion Select an answer first
5. 24application · medium

   A product analyst wants to understand the distribution of product prices. For each product, the analyst needs to know what percentage of products have a price less than or equal to the current product's price. Which function should be used?

   • APERCENT_RANK() OVER (ORDER BY price)
   • BNTILE(100) OVER (ORDER BY price)
   • CROW_NUMBER() OVER (ORDER BY price) / COUNT(*) OVER ()
   • DCUME_DIST() OVER (ORDER BY price)
   Reveal Solution Discussion Select an answer first
6. 25application · medium

   A manager wants to rank employees by sales performance within their region. If two employees have the same sales, they should receive the same rank, and the next rank should be the next consecutive number (no gaps). Which function should be used?

   • ADENSE_RANK() OVER (PARTITION BY region_id ORDER BY sales DESC)
   • BRANK() OVER (PARTITION BY region_id ORDER BY sales DESC)
   • CROW_NUMBER() OVER (PARTITION BY region_id ORDER BY sales DESC)
   • DNTILE(10) OVER (PARTITION BY region_id ORDER BY sales DESC)
   Reveal Solution Discussion Select an answer first