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
Which window function assigns the same rank to ties but leaves gaps in the sequence afterward?
Answer: RANK()
RANK() gives tied rows the same rank and skips the next ranks, leaving gaps.
What does NTILE(4) do when applied over an ordered partition?
Answer: Divides rows into 4 roughly equal buckets
NTILE(n) distributes ordered rows into n approximately equal groups.
In LAG(salary, 2) OVER (ORDER BY hire_date), which row's value is returned?
Answer: The row 2 positions before
LAG with offset 2 accesses the value from two rows prior in the ordering.
Which clause must accompany RANK() for deterministic results?
Answer: ORDER BY inside OVER
Ranking functions require an ORDER BY in the OVER clause to define the sequence.
What is returned by FIRST_VALUE(price) OVER (PARTITION BY category ORDER BY price)?
Answer: The lowest price in each category
FIRST_VALUE returns the first row's value per the ordering, here the lowest price per category.
Which function would you use to compute a running total of sales by date?
Answer: SUM() OVER (ORDER BY date)
SUM() with an ORDER BY in OVER produces a cumulative running total.
What does an empty OVER () clause cause an aggregate like AVG() to do?
Answer: Compute over the entire result set as one window
An empty OVER () treats all rows as a single window, returning the overall aggregate on each row.