Skip to Content

Window Functions

When to use

Compute a value per row that depends on a SET of related rows — without collapsing them via GROUP BY.

Analogy

A window function is ‘given my neighbourhood, what’s my rank’ — the neighbourhood is the PARTITION BY, the order is the ORDER BY.

Data-flow diagram

  PARTITION BY user_id       -- the neighbourhood
  ORDER BY ts                -- the direction
  ROWS BETWEEN ...           -- the frame (which rows count)

      +-----+-----+-----+-----+-----+-----+-----+
      | U1  | U1  | U2  | U2  | U2  | U3  | U3  |
      +-----+-----+-----+-----+-----+-----+-----+
          ^partition         ^partition

Deep explanation

Window functions compute per row across a window of related rows, leaving the original rows intact. PARTITION BY defines the neighbourhood; ORDER BY defines rank direction; the frame clause (ROWS BETWEEN … PRECEDING AND … FOLLOWING) defines the slice the function sees. ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, SUM/AVG-as-window are the workhorses. Window functions are the cleanest way to make sequence features (time since last event, per-user rank, deduplication).

Examples

Example 1

-- 7a: per-user ranking by event time
SELECT event_id, user_id, ts,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) AS r
  FROM events;

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) is the canonical ‘most recent per user’ pattern — beats DISTINCT ON for ties.

Example 2

-- 7b: rolling 7-day window sum per user
SELECT user_id, ts,
       SUM(amount) OVER (
         PARTITION BY user_id
         ORDER BY ts
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7_sum
  FROM events;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW gives a true 7-row window; RANGE BETWEEN INTERVAL ‘7 days’ PRECEDING gives time-aware windows (depends on frame semantics).

Example 3

-- 7c: 'time since previous event' feature
SELECT user_id, ts,
       ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) AS gap
  FROM events;

LAG fetches the previous row’s ts in the same partition — instant feature for sequence modelling.

Common mistake

Confusing ROWS vs RANGE frames: ROWS counts rows; RANGE uses peer rows with the same ORDER BY value (or interval). Picking the wrong frame produces subtly different rolling sums. Another: forgetting ORDER BY inside the OVER clause — when omitted, the frame is the entire partition, which is almost never what you want for time-series.

Key takeaway

ROW_NUMBER for dedup/ranking; PARTITION BY defines the neighbourhood; ORDER BY drives the frame direction; ROWS vs RANGE matters for time windows; LAG/LEAD for sequence features.

Production Failure Playbook

Failure scenario 1: rank-tie-broken-wrong

Failure scenario 2: window-on-no-partition