Advanced Window Functions Flashcards

Can window functions be used directly in a WHERE clause?

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

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

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

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

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

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

Answer: After GROUP BY and HAVING, before ORDER BY

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

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

Answer: An explicit frame like ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

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

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

Answer: The salary from the 3rd row of the frame

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

How can you reuse the same window specification across multiple functions?

Answer: Define a named WINDOW clause

A named WINDOW clause lets multiple functions share one OVER specification.

What happens to NULL values by default in an ORDER BY within OVER (in standard SQL)?

Answer: Their position depends on NULLS FIRST/LAST or the engine default

NULL ordering follows NULLS FIRST/LAST specification or the database's default behavior.