SQL Window Functions Cheatsheet

SUM(amount) OVER (PARTITION BY user_id ORDER BY ts ROWS UNBOUNDED PRECEDING)

ROW_NUMBER: unique sequential.
RANK: same value → same rank, leaves gaps.
DENSE_RANK: same value → same rank, no gaps.

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY score DESC) rn
  FROM t
) x WHERE rn <= 3;

LAG(price) OVER (PARTITION BY symbol ORDER BY ts)

LEAD(price) OVER (PARTITION BY symbol ORDER BY ts)

(price - LAG(price) OVER (ORDER BY ts)) * 100.0 / LAG(price) OVER (ORDER BY ts)

AVG(x) OVER (ORDER BY ts ROWS UNBOUNDED PRECEDING)

AVG(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

RANK() OVER (PARTITION BY category ORDER BY score DESC)

FIRST_VALUE(x) OVER (PARTITION BY g ORDER BY ts) and LAST_VALUE(x) OVER (PARTITION BY g ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — LAST_VALUE returns the current row! Override the frame.

PERCENT_RANK() OVER (ORDER BY score) — value 0..1, percentile within partition.