Chapter 12 — Quiz

13 questions. Answers at the bottom.

Multiple choice

Q1. Unlike GROUP BY, a window function: - A) Removes duplicate rows - B) Computes over related rows without collapsing them - C) Can only count - D) Requires a join

Q2. PARTITION BY in an OVER clause: - A) Deletes rows - B) Splits rows into groups for the function, without collapsing - C) Sorts the whole table - D) Is the same as WHERE

Q3. Which gives a unique sequential number per partition, even for ties? - A) RANK() - B) DENSE_RANK() - C) ROW_NUMBER() - D) NTILE()

Q4. RANK() handles ties by: - A) Assigning the same rank, then skipping (1,1,3) - B) Assigning the same rank, no gaps (1,1,2) - C) Assigning unique ranks - D) Erroring

Q5. SUM(amount) OVER (ORDER BY order_date) produces: - A) The grand total on every row - B) A running (cumulative) total - C) The same as GROUP BY - D) An error

Q6. SUM(price) OVER () (empty parentheses) gives: - A) A running total - B) The grand total of price on every row - C) NULL - D) Per-category totals

Q7. LAG(revenue) OVER (ORDER BY month) returns: - A) The next month's revenue - B) The previous month's revenue - C) The maximum revenue - D) The current revenue

Q8. You cannot reference a window function in: - A) The SELECT list - B) ORDER BY - C) WHERE (compute it in a subquery/CTE, then filter) - D) A CTE

Q9. With ORDER BY but no explicit frame, the default frame is: - A) The whole partition - B) From the start up to the current row (cumulative) - C) Current row only - D) The next row

Q10. LAST_VALUE often returns the current row instead of the last because: - A) It's buggy - B) The default frame ends at the current row; set ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING - C) It needs a PARTITION BY - D) It only works on numbers

Q11. The clean solution to "top N rows per group" is: - A) GROUP BY + HAVING - B) ROW_NUMBER() in a subquery/CTE, filtered in the outer query - C) DISTINCT - D) A cross join

True/False

Q12. A running total via a window function is typically far faster than via a correlated subquery. (True / False)

Q13. ROWS and RANGE frame modes always behave identically. (True / False)

---

Answer key

Q1 — B. Window functions keep every row while computing over a related set.

Q2 — B. PARTITION BY groups without collapsing (the row-preserving cousin of GROUP BY).

Q3 — C. ROW_NUMBER() is always unique per partition.

Q4 — A. RANK repeats then skips (1,1,3); DENSE_RANK repeats without gaps (1,1,2).

Q5 — B. Adding ORDER BY to a windowed aggregate makes it cumulative.

Q6 — B. OVER () = whole result is the window → grand total on each row.

Q7 — B. LAG looks back (previous row); LEAD looks forward.

Q8 — C. Window functions run after WHERE; filter them via a wrapping subquery/CTE.

Q9 — B. Default with ORDER BY is RANGE UNBOUNDED PRECEDING ... CURRENT ROW (cumulative).

Q10 — B. The default frame ends at the current row; use an explicit full frame for the true last value.

Q11 — B. Rank with ROW_NUMBER(), then filter on the rank in an outer query.

Q12 — True. Window = one pass (O(n)); correlated subquery for a running total is O(n²).

Q13 — False. They differ with ties: ROWS counts physical rows; RANGE groups peers with equal ORDER BY values.

Scoring: 11–13 you've unlocked the power tool; 8–10 re-drill ranking and running totals; below 8, redo Exercises B–C.