Window functions — quiz

The main difference between a window function and GROUP BY:

1. Speed
2. Window functions retain every row
3. Windows only work on numbers
4. GROUP BY is deprecated

Answer: Window functions retain every row — That retention is the whole point.

Why must a top-N-per-group query use a subquery?

1. Performance
2. Window functions cannot be used in WHERE
3. PARTITION BY requires it
4. To avoid nulls

Answer: Window functions cannot be used in WHERE — Windows evaluate after WHERE, so the filter happens one level up.

SUM(x) OVER (ORDER BY d) returns a running total because:

1. ORDER BY sorts output
2. ORDER BY changes the default frame to unbounded preceding through current row
3. SUM behaves differently in windows
4. PARTITION BY is missing

Answer: ORDER BY changes the default frame to unbounded preceding through current row — The default frame changes as soon as ORDER BY appears.

With duplicate dates, RANGE differs from ROWS because:

1. RANGE is faster
2. RANGE includes all rows sharing the current ORDER BY value
3. ROWS ignores nulls
4. They are identical

Answer: RANGE includes all rows sharing the current ORDER BY value — That is why moving averages should specify ROWS.

Values 100, 90, 90, 80 with RANK() DESC give:

1. 1,2,2,3
2. 1,2,2,4
3. 1,2,3,4
4. 1,1,2,3

Answer: 1,2,2,4 — RANK ties then skips the intervening position.

To keep the latest row per email, use:

1. DISTINCT
2. ROW_NUMBER partitioned by email, ordered by updated_at DESC, filtered to 1
3. GROUP BY email
4. RANK filtered to 1

Answer: ROW_NUMBER partitioned by email, ordered by updated_at DESC, filtered to 1 — RANK could return several rows on a tie; ROW_NUMBER returns exactly one.

LAG(revenue) on the first row returns:

1. 0
2. NULL unless a default is supplied
3. The last row
4. An error

Answer: NULL unless a default is supplied — The third argument supplies a default.

A month with no data causes LAG to:

1. Error
2. Compare across the gap silently
3. Return zero
4. Skip the query

Answer: Compare across the gap silently — Join to a date spine so every period has a row.

Which frame gives a running total?

1. ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
2. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
3. RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
4. No frame at all

Answer: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — Everything from the start of the partition to the current row.

For a 3-period moving average you should specify ROWS because:

1. It is faster
2. RANGE would include tied ordering values
3. ROWS handles nulls
4. RANGE is invalid

Answer: RANGE would include tied ordering values — Ties on the ORDER BY column would widen the window under RANGE.