Window functions

Window functions calculate across related rows without reducing them to one row per group.

SELECT customer_id,
       ordered_at,
       net_amount,
       sum(net_amount) OVER (
         PARTITION BY customer_id
         ORDER BY ordered_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS lifetime_value
FROM sales.orders;

Window specification

function(...) OVER (
  [PARTITION BY expression, ...]
  [ORDER BY expression [ASC | DESC] [NULLS FIRST | NULLS LAST], ...]
  [ROWS | RANGE | GROUPS frame]
)

Named windows avoid repeating a specification:

SELECT *, row_number() OVER w AS n, sum(amount) OVER w AS running_amount
FROM payments
WINDOW w AS (PARTITION BY account_id ORDER BY paid_at);

Ranking and distribution

row_number, rank, dense_rank, percent_rank, cume_dist and ntile assign position or relative distribution within the ordered partition.

Navigation and value selection

lag, lead, first_value, last_value and nth_value access another value in the window. lag and lead accept optional offset and default arguments.

SELECT day, revenue,
       revenue - lag(revenue, 1, 0) OVER (ORDER BY day) AS change
FROM daily_revenue;

fill(expression ORDER BY ordering) linearly interpolates missing values within the ordered partition where the argument and ordering types admit interpolation. Leading or trailing gaps without bounding values remain subject to the function's NULL behavior.

Aggregate windows

Aggregate functions such as sum, avg, count, min, max, list and statistical aggregates can be used with OVER. The window frame, not GROUP BY, determines their input rows.

Frames

• ROWS counts physical rows relative to the current row.
• RANGE uses the ordering value and is useful for time/value ranges.
• GROUPS counts peer groups with equal ordering values.

Bounds are UNBOUNDED PRECEDING, expression PRECEDING, CURRENT ROW, expression FOLLOWING and UNBOUNDED FOLLOWING. Exclusion options can remove the current row, its peer group or ties where supported.

QUALIFY

QUALIFY filters after window evaluation without requiring a subquery.

SELECT *
FROM sales.orders
QUALIFY row_number() OVER (
  PARTITION BY customer_id ORDER BY ordered_at DESC
) = 1;