Growth Notes

Window Functions

Tags: web-dev analytics concept

Window Functions

Date: 2026-09-27

A calculation across related rows that keeps every row. GROUP BY collapses a group to one line; OVER (...) looks at the group and writes the answer onto each member. Most of analytics — sessionising, ranking, running totals, “previous order”, deduplication — is this.

A window function computes a value for each row from a set of rows related to it — its window — without merging those rows together.

Same data, both ways

orders
customer  ordered_at  total
ana       2026-01-03  40
ana       2026-02-10  25
ana       2026-05-01  60
ben       2026-01-20  30

GROUP BY customer                     SUM(total) OVER (PARTITION BY customer)

customer  spend                       customer  ordered_at  total  spend
ana       125                         ana       2026-01-03  40     125
ben       30                          ana       2026-02-10  25     125
                                      ana       2026-05-01  60     125
rows collapsed                        ben       2026-01-20  30     30
                                      every row kept, answer repeated

Now each order can be compared with its customer’s total — total / spend is each order’s share — which GROUP BY can’t do without joining the aggregate back on.

The anatomy

function() OVER (
  PARTITION BY customer        -- which rows belong together (optional: whole table)
  ORDER BY ordered_at          -- order inside each partition (needed for ranks, LAG, running totals)
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- the frame (optional)
)
  • PARTITION BY is the grouping. Leave it out and the window is the whole result
  • ORDER BY inside OVER is separate from the query’s own ORDER BY. It orders the window, not the output
  • The frame narrows the window to a range around the current row. Needed for running totals and moving averages; irrelevant to ROW_NUMBER and LAG

The functions worth knowing

ana's three orders, PARTITION BY customer ORDER BY ordered_at

ordered_at  total  ROW_NUMBER  LAG(ordered_at)  running SUM  FIRST_VALUE(ordered_at)
2026-01-03  40     1           NULL             40           2026-01-03
2026-02-10  25     2           2026-01-03       65           2026-01-03
2026-05-01  60     3           2026-02-10       125          2026-01-03
  • ROW_NUMBER() — 1, 2, 3 within each partition. First order, latest event, deduplication
  • RANK() / DENSE_RANK() — like ROW_NUMBER, but ties share a number. RANK leaves gaps after a tie (1, 1, 3); DENSE_RANK doesn’t (1, 1, 2)
  • LAG(x) / LEAD(x) — the value from the previous or next row. Days between orders, the page before this one
  • SUM / AVG / COUNT with a frame — running totals, seven-day moving averages
  • FIRST_VALUE / LAST_VALUE — the acquisition channel of a customer’s first order, stamped on every order. LAST_VALUE needs an explicit frame, or it returns the current row — see below
  • NTILE(n) — splits a partition into n buckets. Deciles of customers by spend — RFM Segmentation

The analytics jobs it does

Sessionising a raw event stream. Gap from the previous event, a flag where the gap exceeds 30 minutes, then a running sum of the flags is the session number:

WITH gaps AS (
  SELECT user_id, event_time,
    CASE
      WHEN LAG(event_time) OVER w IS NULL THEN 1                         -- first event starts a session
      WHEN TIMESTAMP_DIFF(event_time, LAG(event_time) OVER w, MINUTE) > 30 THEN 1  -- long gap starts one
      ELSE 0
    END AS is_new_session
  FROM events
  WINDOW w AS (PARTITION BY user_id ORDER BY event_time)
)
SELECT *,
  SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_number
  -- running total of the 1s: 1,1,2,3 → each 1 bumps the session count
FROM gaps;

TIMESTAMP_DIFF is BigQuery’s spelling; Postgres subtracts timestamps and reads the interval — SQL Dialects. This is the whole mechanism behind Sessionisation done at query time, and why a warehouse can re-sessionise history at a different timeout — Warehouse-First Analytics.

Deduplicating events — keep one row per event_id, the earliest:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY received_at) AS rn
  FROM events
) WHERE rn = 1;

Where the dialect supports it, QUALIFY rn = 1 does the same without the subquery — Idempotency and Deduplication.

Also routinely: first-touch channel per customer, order number (first vs repeat purchase), days since previous order for Cohort Analysis, and percent of total.

Why you can’t filter on one in WHERE

SELECT *, ROW_NUMBER() OVER (...) AS rn FROM events WHERE rn = 1;   -- error

Window functions are computed after WHERE, GROUP BY and HAVING — nearly last, just before ORDER BY and LIMIT. So WHERE can’t see them, and filtering needs a subquery or CTE wrapped around them, or QUALIFY — Aggregation and Grouping · Common Table Expressions.

The same ordering means a WHERE clause changes the window. Filtering to March before ROW_NUMBER makes a customer’s first March order “order 1”, which isn’t their first order.

Traps

  • The default frame. With ORDER BY in the window and no frame stated, the frame is “start of partition to the current row” — which is why SUM(...) OVER (ORDER BY ...) is a running total, not a total. It’s also why LAST_VALUE returns the current row. State the frame when it matters: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  • ROWS vs RANGE. ROWS counts physical rows. RANGE treats rows with equal ORDER BY values as one step, so ties all get the same running total. The default frame is RANGE, which surprises people with duplicate timestamps
  • Ties make ROW_NUMBER non-deterministic. Two events with the same timestamp can swap between runs. Add a tie-breaker column to the ORDER BY
  • Cost. Each distinct PARTITION BY ... ORDER BY is a sort. On a large events table several different windows mean several sorts — reuse one named WINDOW clause where the dialect allows — Query Performance

Graph View

Backlinks