Advanced Window Functions Flashcards
7 cards from real SQL practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 7 Advanced Window Functions flashcards as text
To compute a 3-day moving average of revenue, which frame is appropriate?
Answer: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
Averaging the current and two prior rows gives a trailing 3-row moving average.
What is the difference between RANK() and DENSE_RANK() output for values 10,10,20?
Answer: RANK gives 1,1,3 and DENSE_RANK gives 1,1,2
RANK skips to 3 after a tie while DENSE_RANK continues to 2 with no gap.
Which is true about PARTITION BY in a window function?
Answer: It divides rows into independent groups for the calculation
PARTITION BY splits rows into groups within which the window function operates independently.
What does SUM(amount) OVER (PARTITION BY user ORDER BY ts ROWS UNBOUNDED PRECEDING) produce?
Answer: A running cumulative sum per user
Ordering with UNBOUNDED PRECEDING to current row yields a per-user running total.
Why can chaining window functions sometimes require a CTE?
Answer: Window functions cannot be nested directly in one another
You cannot nest a window function inside another, so a CTE materializes the first result for the second.
What does PERCENT_RANK() return for the very first row of an ordered partition?
Answer: 0
PERCENT_RANK is (rank-1)/(rows-1), so the first row always yields 0.
Which scenario is best solved by LAG to compare consecutive periods?
Answer: Calculating month-over-month change
LAG retrieves the prior period's value, enabling month-over-month difference calculations.