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

Questions 11–15

1. 11application · medium

   A marketing analyst wants to divide customers into 4 equal-sized groups based on their total spending, from lowest to highest, so each group can receive a different promotional offer. Which function should be used to assign each customer to a group (1 through 4)?

   • APERCENT_RANK() OVER (ORDER BY total_spending)
   • BROW_NUMBER() OVER (ORDER BY total_spending)
   • CNTILE(4) OVER (ORDER BY total_spending)
   • DCUME_DIST() OVER (ORDER BY total_spending)
   Reveal Solution Discussion Select an answer first
2. 12application · medium

   A financial analyst needs to show, for each transaction, the amount of the next transaction for the same account. The table has columns account_id, transaction_date, and amount. Which function should be used?

   • ALEAD(amount) OVER (PARTITION BY account_id ORDER BY transaction_date)
   • BLAG(amount) OVER (PARTITION BY account_id ORDER BY transaction_date)
   • CNTH_VALUE(amount, 2) OVER (PARTITION BY account_id ORDER BY transaction_date)
   • DFIRST_VALUE(amount) OVER (PARTITION BY account_id ORDER BY transaction_date)
   Reveal Solution Discussion Select an answer first
4. 13foundation · easy

   Given a partition with values (10, 20, 20, 30), which ranking function assigns the ranks 1, 2, 2, 4?

   • ADENSE_RANK()
   • BNTILE(4)
   • CROW_NUMBER()
   • DRANK()
   Reveal Solution Discussion Select an answer first
5. 14expert · hard

   An analyst is calculating a running total of sales, but the data contains duplicate dates. The analyst wants the running total to include all rows with the same date, not just the current row. Which frame specification should be used?

   • AROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   • BROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
   • CRANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   • DRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   Reveal Solution Discussion Select an answer first
6. 15application · hard

   An analyst needs to find the first and last order date for each customer from a table of orders. The analyst wants to use a single query with window functions. Which pair of functions will retrieve the earliest and latest order dates for each customer?

   • ALAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) and LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
   • BMIN(order_date) OVER (PARTITION BY customer_id) and MAX(order_date) OVER (PARTITION BY customer_id)
   • CFIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) and LAST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
   • DFIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date DESC) and LAST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date DESC)
   Reveal Solution Discussion Select an answer first