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

Questions 6–10

1. 6foundation · easy

   Which aggregate function can be used as an analytic function to compute a running total?

   • AAVG()
   • BCOUNT()
   • CSUM()
   • DMAX()
   Reveal Solution Discussion Select an answer first
2. 7foundation · medium

   In Snowflake, what is the defining difference between an analytic (window) function and a regular aggregate function?

   • AAn analytic function requires a PARTITION BY clause, while a regular aggregate function does not require any grouping clause.
   • BAn analytic function returns one result row per input row, while a regular aggregate function returns one result row per group.
   • CAn analytic function always returns a constant value for all rows in a partition, while a regular aggregate function returns a different value for each row.
   • DAn analytic function can only be used in the SELECT clause, while a regular aggregate function can be used in WHERE and HAVING clauses.
   Reveal Solution Discussion Select an answer first
4. 8foundation · easy

   What is the difference between ROWS and RANGE window frames?

   • AROWS includes the current row, while RANGE excludes it.
   • BROWS includes a fixed number of rows, while RANGE includes rows based on the value of the ORDER BY column.
   • CROWS works only with numeric data, while RANGE works with any data type.
   • DROWS can only be used with ORDER BY, while RANGE can be used without ORDER BY.
   Reveal Solution Discussion Select an answer first
5. 9application · medium

   A data analyst needs to compare each employee's current salary to the salary they had two performance reviews ago. The table has one row per employee per review, with columns employee_id, review_date, and salary. Which function and OVER clause will retrieve the salary from two reviews back for each employee?

   • ALEAD(salary, 2) OVER (PARTITION BY employee_id ORDER BY review_date)
   • BFIRST_VALUE(salary) OVER (PARTITION BY employee_id ORDER BY review_date)
   • CLAG(salary) OVER (PARTITION BY employee_id ORDER BY review_date)
   • DLAG(salary, 2) OVER (PARTITION BY employee_id ORDER BY review_date)
   Reveal Solution Discussion Select an answer first
6. 10foundation · easy

   Which function returns the value at a specified percentile, interpolating between values if needed?

   • APERCENTILE_CONT()
   • BPERCENT_RANK()
   • CCUME_DIST()
   • DNTILE()
   Reveal Solution Discussion Select an answer first