Window Functions & GROUPING — Series 2

Preview — 3 of 10 questions

For a partition of 5 rows with no ties, what does PERCENT_RANK() OVER (ORDER BY score) return for the row with the lowest score?

javascript

SELECT name, score,
  PERCENT_RANK() OVER (ORDER BY score) AS pct_rank
FROM contestants;

ANULL, since PERCENT_RANK requires PARTITION BY

B0.2 (i.e., 1 ⁄ 5)

What does the WINDOW clause let you do here?

javascript

SELECT
  name,
  RANK() OVER w AS rnk,
  SUM(salary) OVER w AS dept_total
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

AIt defines a reusable named window specification once, which multiple window functions in the same query can reference via OVER w, avoiding repetition

BIt creates a materialized view of the windowed result

CWINDOW is required syntax whenever more than one window function appears in a query

DIt changes the window frame from RANGE to ROWS automatically

LAST_VALUE famously needs an explicit frame extension (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) to return the true last row of the partition. Does FIRST_VALUE have the same problem?

javascript

SELECT name, salary,
  FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_sal
FROM employees;

AYes — FIRST_VALUE has exactly the same default-frame problem as LAST_VALUE and also requires ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

BFIRST_VALUE cannot be used together with PARTITION BY

CFIRST_VALUE always returns NULL unless an explicit frame is given

DNo — with the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), FIRST_VALUE already correctly returns the first row of the partition for every row, because the frame's lower bound is always the partition start, regardless of the current row

Answer all 10 questions (7 more), see explanations for every answer, and track your score.