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

Questions 16–20

1. 16application · medium

   A data scientist needs to find the median salary for each department. The analyst wants to use a window function to compute this. Which function should be used?

   • APERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary)
   • BNTILE(2) OVER (PARTITION BY department_id ORDER BY salary)
   • CPERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department_id)
   • DCUME_DIST() OVER (PARTITION BY department_id ORDER BY salary)
   Reveal Solution Discussion Select an answer first
2. 17foundation · easy

   Which function divides the rows in a partition into a specified number of buckets and assigns a bucket number to each row?

   • APERCENT_RANK()
   • BCUME_DIST()
   • CNTILE()
   • DPERCENTILE_CONT()
   Reveal Solution Discussion Select an answer first
4. 18application · medium

   An analyst is calculating a 3-day moving average of daily sales. The query uses SUM(sales) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). The first day of the dataset has no preceding rows. What will the moving average include for that first day?

   • AThe first day's sales plus the next two days' sales
   • BA NULL value because the frame is incomplete
   • COnly the first day's sales value
   • DAn error because the frame specification is invalid
   Reveal Solution Discussion Select an answer first
5. 19expert · hard

   An analyst is writing a query to find the second-highest salary in each department. The query uses DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dr. Which clause will correctly filter to only the second-highest salary per department?

   • AQUALIFY dr = 2
   • BORDER BY dr LIMIT 1
   • CWHERE dr = 2
   • DHAVING dr = 2
   Reveal Solution Discussion Select an answer first
6. 20foundation · easy

   Which clause can be used to filter the results of an analytic function, such as keeping only rows where ROW_NUMBER() = 1?

   • AHAVING
   • BWHERE
   • CQUALIFY
   • DGROUP BY
   Reveal Solution Discussion Select an answer first