SQL Advanced Window Functions 4

Question 1

Can window functions be used directly in a WHERE clause?

Accepted Answer

No, you must use a subquery or CTE to filter on them

Answer

Window functions are evaluated after WHERE, so filtering on them requires wrapping in a subquery or CTE.

Question 2

What is a common pattern for selecting the top row per group using window functions?

Accepted Answer

ROW_NUMBER() OVER (PARTITION BY g ORDER BY x) and filter = 1

Answer

Assigning ROW_NUMBER per partition and keeping row number 1 yields the top row per group.

Question 3

Which evaluation phase runs window functions relative to GROUP BY and HAVING?

Accepted Answer

After GROUP BY and HAVING, before ORDER BY

Answer

Window functions execute after grouping and HAVING but before the final ORDER BY.

Question 4

What does LAST_VALUE typically require to return the true final value of a partition?

Accepted Answer

An explicit frame like ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Answer

Because the default frame ends at the current row, LAST_VALUE needs a frame extending to UNBOUNDED FOLLOWING.

Question 5

What does NTH_VALUE(salary, 3) OVER (...) return?

Accepted Answer

The salary from the 3rd row of the frame

Answer

NTH_VALUE returns the value from the nth row of the window frame.

SQL Practice Test