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

Questions 1–5

1. 1application · medium

   An analyst needs to find the top 3 highest-paid employees in each department. The query uses ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn. Which clause should be used to filter the results to only the top 3 per department?

   • AWHERE rn <= 3
   • BORDER BY rn LIMIT 3
   • CQUALIFY rn <= 3
   • DHAVING rn <= 3
   Reveal Solution Discussion Select an answer first
2. 2expert · hard

   A data analyst needs to divide employees into 4 quartiles based on their performance score, but the analyst wants the quartile assignment to be based on the score value, not the number of employees. Which function should be used?

   • ARANK() OVER (ORDER BY score) / COUNT(*) OVER ()
   • BCASE WHEN score <= PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY score) OVER () THEN 1 WHEN score <= PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score) OVER () THEN 2 ... END
   • CNTILE(4) OVER (ORDER BY score)
   • DPERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY score) OVER ()
   Reveal Solution Discussion Select an answer first
4. 3foundation · easy

   Which function returns the first value in an ordered window frame?

   • AFIRST_VALUE()
   • BLEAD()
   • CNTH_VALUE()
   • DLAG()
   Reveal Solution Discussion Select an answer first
5. 4foundation · easy

   In which order are the following clauses evaluated in a Snowflake query that uses analytic functions?

   • AWHERE, GROUP BY, analytic functions, HAVING, QUALIFY, ORDER BY
   • BQUALIFY, WHERE, GROUP BY, HAVING, analytic functions, ORDER BY
   • CGROUP BY, WHERE, HAVING, analytic functions, QUALIFY, ORDER BY
   • DWHERE, GROUP BY, HAVING, analytic functions, QUALIFY, ORDER BY
   Reveal Solution Discussion Select an answer first
6. 5application · hard

   An analyst needs to compute a 7-day moving average of website traffic, but the data has missing days (no rows for some dates). The analyst wants the average to consider all calendar days, not just the days with data. Which frame specification should be used?

   • AROWS BETWEEN 6 PRECEDING AND CURRENT ROW
   • BRANGE BETWEEN 6 PRECEDING AND CURRENT ROW
   • CRANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW
   • DROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   Reveal Solution Discussion Select an answer first