Window Functions & GROUPING — Series 3

Preview — 3 of 10 questions

What does CUME_DIST() OVER (ORDER BY score) compute for each row?

AThe running total of score up to and including that row

BThe row's rank divided by the total number of distinct scores

CThe cumulative distribution: the fraction of rows in the partition whose score is less than or equal to the current row's score — always a value in (0, 1], with the maximum score's row always getting exactly 1

DThe percentage difference between this row's score and the row before it

Given results ordered by score DESC within each team, what does this compute?

javascript

NTH_VALUE(player_name, 2) OVER (PARTITION BY team ORDER BY score DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

AThe name of the player with the 2nd-highest score on each team

BThe 2nd row's score for every team, regardless of ranking

CA syntax error — NTH_VALUE requires exactly 2 arguments and none may be a column

DThe number of players tied for 2nd place

What does this compute for each day's revenue?

javascript

SELECT day, revenue,
  AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS trailing_avg
FROM daily_sales;

AThe average revenue across all days, repeated on every row

BA syntax error — numeric frame bounds like 2 PRECEDING require a RANGE frame, not ROWS

CThe average of exactly 2 days: the 2 days before the current one, excluding the current day

DA 3-day trailing moving average — for each row, the average of that day's revenue plus the 2 days immediately before it (fewer days averaged near the very start, since there's nothing before day 1)

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