The four SQL window functions that do 90% of the work

Window functions are the point where SQL stops being a chore and starts being a power tool. They compute a value for each row using other rows — without collapsing the result the way GROUP BY does. Four of them cover the vast majority of real analytical work.

1. ROW_NUMBER — “the latest record per entity”

The single most common analytics problem: a table has multiple rows per customer (or device, or order) and you want the most recent one. Every query in this article runs against the sample data — two CSVs, a subscriptions table and an events table, queryable straight from the web.

SELECT *
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY signed_up_at DESC
    ) AS rn
  FROM subscriptions
) t
WHERE rn = 1;

PARTITION BY restarts the numbering per customer; ORDER BY ... DESC makes the newest row number 1. A customer who cancelled and came back has two rows here, and this keeps the current one. The same shape deduplicates event logs: partition by the natural key, order by ingestion time, keep rn = 1.

Related: RANK leaves gaps on ties, DENSE_RANK doesn’t. For “top 3 products per category” use DENSE_RANK() <= 3 and decide explicitly how ties should behave, because they will occur.

The subquery is boilerplate. Snowflake, BigQuery, DuckDB, and Databricks let you drop it with QUALIFY, which filters on a window function’s result the way HAVING filters on an aggregate:

SELECT *
FROM subscriptions
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id ORDER BY signed_up_at DESC
) = 1;

Same plan, half the query. Postgres and MySQL have no QUALIFY; keep the subquery there. One trap survives both forms: if two rows share the same signed_up_at, rn = 1 picks one arbitrarily, and “arbitrarily” can change between runs on the same data. A date column makes ties likely rather than exotic, so break them explicitly — ORDER BY signed_up_at DESC, subscription_id DESC.

2. LAG — “compare each row to the previous one”

LAG(col) fetches a value from the previous row in the window. It turns a log of states into a log of changes:

SELECT
  customer_id,
  plan,
  LAG(plan) OVER (
    PARTITION BY customer_id ORDER BY signed_up_at
  ) AS previous_plan,
  signed_up_at
FROM subscriptions

Filter for plan <> previous_plan and you have every upgrade and downgrade. That filter has a hole in it, though. At the first row of each partition LAG returns NULL, and NULL <> 'pro' is NULL, not true — so every customer’s first plan disappears from the change log. LAG takes two more arguments that fix it: LAG(plan, 1, 'none') looks one row back and returns 'none' at the partition edge. Now the first row compares cleanly and signups stop vanishing.

The same pattern computes time between events — the backbone of sessionisation:

-- New session when the gap since the previous event exceeds 30 minutes
SELECT
  customer_id,
  occurred_at,
  CASE
    WHEN occurred_at - LAG(occurred_at) OVER (
      PARTITION BY customer_id ORDER BY occurred_at
    ) > INTERVAL '30 minutes'
    THEN 1 ELSE 0
  END AS session_start
FROM events

A running SUM over session_start then gives each event a session id. Two window functions, and you’ve replaced a product analytics vendor’s core feature. The sample data ships its own session_id, so you can check the sessions you derived against the ones that were recorded.

3. SUM(…) OVER — running totals and share-of-total

With an ORDER BY inside the window, SUM becomes cumulative:

WITH daily_revenue AS (
  SELECT occurred_at::date AS order_date, SUM(amount) AS revenue
  FROM events
  WHERE event_type = 'purchase'
  GROUP BY 1
)
SELECT
  order_date,
  revenue,
  SUM(revenue) OVER (ORDER BY order_date) AS revenue_to_date
FROM daily_revenue

Without ORDER BY, it’s the total over the whole partition — which gives you share-of-total in one pass, no join back to an aggregate:

WITH latest_subscription AS (
  SELECT customer_id, country, channel
  FROM subscriptions
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY customer_id ORDER BY signed_up_at DESC
  ) = 1
),
revenue_by_channel AS (
  SELECT s.country, s.channel, SUM(e.amount) AS revenue
  FROM events e
  JOIN latest_subscription s USING (customer_id)
  WHERE e.event_type = 'purchase'
  GROUP BY 1, 2
)
SELECT
  country,
  channel,
  revenue,
  revenue / SUM(revenue) OVER () AS share_of_total,
  revenue / SUM(revenue) OVER (PARTITION BY country) AS share_of_country
FROM revenue_by_channel

The latest_subscription step is not decoration. Join events to subscriptions directly and every customer with two subscriptions counts their revenue twice — pattern 1 is what stops it.

The running total above hides a default that surprises people the first time order_date is not unique. Take four rows:

SUM(revenue) OVER (ORDER BY order_date) returns 100, 600, 600, 650. Both 2026-03-02 rows get 600, because ORDER BY with no frame gets a default one: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE works on values, so rows sharing an order_date are peers and all see each other. Adding ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW returns 100, 400, 600, 650 instead. Neither is wrong — a per-day cumulative total genuinely should treat the tied rows alike. Getting one when you meant the other is wrong. If the ordering column has duplicates, state the frame.

4. AVG with a frame — moving averages

The frame clause (ROWS BETWEEN ...) restricts the window to a sliding range:

WITH daily_signups AS (
  SELECT signed_up_at AS day, COUNT(*) AS signups
  FROM subscriptions
  GROUP BY 1
)
SELECT
  day,
  signups,
  AVG(signups) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS signups_7d_avg
FROM daily_signups

One caveat worth internalising: ROWS BETWEEN 6 PRECEDING means “the previous six rows”, not “the previous six days”. If dates are missing from the table, those aren’t the same thing — and a GROUP BY over signups leaves out every day nobody signed up.

ROWS versus RANGE, worked out

Here is a five-row daily_signups with a hole in it. Nobody signed up on 2026-03-04, so the ETL wrote no row at all:

Ask for a three-day average and read the value at 2026-03-05.

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW counts rows. It reaches back to 2026-03-02 and 2026-03-03, so it averages 52, 61, 55 → 56.0. The window has quietly stretched across four calendar days.

RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW counts values. It takes every row whose day falls in 2026-03-03 to 2026-03-05, which is two rows: 61 and 55 → 58.0. The window is the right width, but the denominator is 2, not 3.

Neither is the number a dashboard means by “3-day average”. That number treats the missing day as a zero: (61 + 0 + 55) / 3 → 38.7. To get it, the hole has to stop existing. Join a calendar table first, then window over the dense result:

WITH daily_signups(day, signups) AS (
  VALUES (DATE '2026-03-01', 40), (DATE '2026-03-02', 52),
         (DATE '2026-03-03', 61), (DATE '2026-03-05', 55),
         (DATE '2026-03-06', 48)
),
calendar AS (
  SELECT day::date AS day
  FROM generate_series(
    DATE '2026-03-01', DATE '2026-03-06', INTERVAL '1 day'
  ) AS g(day)
),
dense AS (
  SELECT c.day, COALESCE(s.signups, 0) AS signups
  FROM calendar c
  LEFT JOIN daily_signups s ON s.day = c.day
)
SELECT
  day,
  signups,
  AVG(signups) OVER (
    ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS signups_3d_avg
FROM dense

With no gaps, ROWS and RANGE agree and both give 38.7. The rule: densify first, then window. RANGE with an interval offset is the fallback when you cannot densify, and it needs a single ORDER BY column of a date, timestamp, or numeric type — it will not accept two sort keys.

FILTER — aggregate a subset inside the window

FILTER (WHERE ...) restricts which rows an aggregate sees. It works on plain aggregates and on window functions, which means you can compute a rolling rate without a self-join or a pile of CASE expressions:

SELECT
  occurred_at::date                               AS day,
  COUNT(*)                                        AS events,
  COUNT(*) FILTER (WHERE event_type = 'purchase') AS purchases,
  SUM(COUNT(*) FILTER (WHERE event_type = 'purchase')) OVER w
    * 1.0 / NULLIF(SUM(COUNT(*)) OVER w, 0) AS purchase_rate_7d
FROM events
GROUP BY day
WINDOW w AS (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
ORDER BY day;

Two things are happening there. FILTER narrows the daily count to purchases. And the window functions take aggregates as their arguments, which is legal because windows run after GROUP BY — one pass gives you the daily numbers and the seven-day rate together. Postgres, DuckDB, and SQLite support FILTER; in MySQL and SQL Server the equivalent is SUM(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END), the same idea with more punctuation.

What a window costs

A window function is a sort plus one pass over the sorted rows. PARTITION BY a ORDER BY b asks the planner for the input sorted by (a, b). Three consequences follow.

Identical specs share one sort. Ten window calls that all say PARTITION BY customer_id ORDER BY occurred_at pay for one sort, not ten. Change the spec on one of them and the whole input gets sorted again. That is the real argument for the named WINDOW w AS (...) clause: it makes an accidental second sort hard to write, and stops copies of the same spec drifting apart.

A matching index can remove the sort. In Postgres an index on (customer_id, signed_up_at DESC) lets WindowAgg read straight from an index scan. EXPLAIN (ANALYZE, BUFFERS) says which case you are in: a Sort node under WindowAgg means you paid, and Sort Method: external merge Disk: 412MB means you paid twice, in CPU and in I/O. Raise work_mem for the session, or add the index.

Not every moving frame is linear. For SUM, COUNT, and AVG over a ROWS frame, Postgres subtracts the row leaving the frame and adds the one entering it, so the query stays linear. MAX and MIN have no such inverse and recompute the whole frame at every row. A 7-row moving max costs nothing; a 90-day moving max over a long history is a different query — read its plan before shipping it.

At some point the right answer is to stop windowing. A running total over all history, recomputed nightly, rereads all history every night: the work grows while the new data does not. Store yesterday’s cumulative value and add today’s, in an incremental table you can prove hasn’t drifted. The window function is still how you backfill it once.

Where windows stop working

The frame can look at rows, never at a value you are in the middle of deriving. That is the wall sessionisation hits when it grows a second rule — “new session after 30 minutes idle, or after 4 hours total”. The cap depends on the session start, which depends on the cap. No frame expresses that; it needs a recursive CTE or a procedural pass, and reaching for one beats nesting six windows and hoping.

Windows also cannot be referenced in WHERE, GROUP BY, or HAVING at the same query level, because they run after those clauses. That one restriction is why rn = 1 needs a subquery, and why QUALIFY exists.

The mental model

Every window function call answers three questions: which rows can this row see (PARTITION BY), in what order (ORDER BY), and how far (the frame clause). When a window query misbehaves, one of those three is wrong — check them in that order.

A good way to practise: take the last three reporting requests you answered with a subquery and a self-join, and rewrite each one with a window function. The rewrites are usually shorter and faster. The ones that resist the rewrite are worth a second look — those often turn out to need a plain GROUP BY after all.