SQL Advanced Window Functions 2 — Questions and Answers
Question 1: Which window function assigns the same rank to ties but leaves gaps in the sequence afterward?
- RANK() (Correct answer)
- DENSE_RANK()
- ROW_NUMBER()
- NTILE()
Correct answer: RANK()
RANK() gives tied rows the same rank and skips the next ranks, leaving gaps.
Question 2: What does NTILE(4) do when applied over an ordered partition?
- Returns the 4th row only
- Divides rows into 4 roughly equal buckets (Correct answer)
- Adds 4 to each row number
- Returns the top 4 rows
Correct answer: Divides rows into 4 roughly equal buckets
NTILE(n) distributes ordered rows into n approximately equal groups.
Question 3: In LAG(salary, 2) OVER (ORDER BY hire_date), which row's value is returned?
- The current row
- The row 2 positions ahead
- The row 2 positions before (Correct answer)
- The first row of the partition
Correct answer: The row 2 positions before
LAG with offset 2 accesses the value from two rows prior in the ordering.
Question 4: Which clause must accompany RANK() for deterministic results?
- GROUP BY
- ORDER BY inside OVER (Correct answer)
- HAVING
- DISTINCT
Correct answer: ORDER BY inside OVER
Ranking functions require an ORDER BY in the OVER clause to define the sequence.
Question 5: What is returned by FIRST_VALUE(price) OVER (PARTITION BY category ORDER BY price)?
- The lowest price in each category (Correct answer)
- The highest price overall
- The average price
- A random price
Correct answer: The lowest price in each category
FIRST_VALUE returns the first row's value per the ordering, here the lowest price per category.
Question 6: Which function would you use to compute a running total of sales by date?
- COUNT() with GROUP BY
- SUM() OVER (ORDER BY date) (Correct answer)
- MAX() OVER ()
- ROW_NUMBER()
Correct answer: SUM() OVER (ORDER BY date)
SUM() with an ORDER BY in OVER produces a cumulative running total.
Question 7: What does an empty OVER () clause cause an aggregate like AVG() to do?
- Error out
- Compute over the entire result set as one window (Correct answer)
- Compute per row only
- Ignore NULLs differently
Correct 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.
SQL Advanced Window Functions 2
Which window function assigns the same rank to ties but leaves gaps in the sequence afterward?