SQL Advanced Window Functions 3 — Questions and Answers
Question 1: What is the default window frame when ORDER BY is specified but no frame clause is given?
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (Correct answer)
- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- The whole partition
Correct answer: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
The SQL default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Question 2: How do ROWS and RANGE frame modes differ when there are duplicate ORDER BY values?
- They are identical
- ROWS counts physical rows; RANGE groups peers with equal values (Correct answer)
- RANGE counts physical rows
- ROWS ignores ties entirely
Correct answer: ROWS counts physical rows; RANGE groups peers with equal values
ROWS treats each row individually while RANGE includes all peer rows sharing the ordering value.
Question 3: What does LEAD(value, 1, 0) return when no following row exists?
- NULL
- The current value
- 0 (Correct answer)
- An error
Correct answer: 0
The third argument supplies a default (0 here) when the offset row does not exist.
Question 4: Which window function returns a value between 0 and 1 representing relative standing?
- PERCENT_RANK() (Correct answer)
- ROW_NUMBER()
- RANK()
- NTILE()
Correct answer: PERCENT_RANK()
PERCENT_RANK() returns a relative rank as a fraction from 0 to 1.
Question 5: In ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, how many rows are in the frame at most?
- 1
- 2
- 3 (Correct answer)
- Unbounded
Correct answer: 3
The frame spans the previous, current, and next rows for a maximum of three.
Question 6: Why might you use a window function instead of GROUP BY for aggregates?
- To collapse rows into one
- To keep individual rows while adding aggregate columns (Correct answer)
- To avoid ORDER BY
- To improve indexing
Correct answer: To keep individual rows while adding aggregate columns
Window functions add aggregate results without collapsing the detail rows.
Question 7: What does CUME_DIST() compute?
- The cumulative count of rows
- The cumulative distribution: fraction of rows at or below the current value (Correct answer)
- The distance between partitions
- The number of distinct values
Correct answer: The cumulative distribution: fraction of rows at or below the current value
CUME_DIST() returns the proportion of rows with a value less than or equal to the current row.
SQL Advanced Window Functions 3
What is the default window frame when ORDER BY is specified but no frame clause is given?