Window Functions Explained

Analogy categoryAnalogies

What Makes Window Functions Different from Aggregates

A regular aggregate function like SUM combined with GROUP BY collapses many rows into one output row per group, discarding the individual rows. A window function, by contrast, computes its result across a defined 'window' of related rows using the OVER clause, but keeps every original row intact in the output, meaning a query can show each individual order alongside that customer's running total without losing any order-level detail. This distinction, computing over a set while preserving row identity, is the single most important thing to internalize before window functions make sense.

🏏

Cricket analogy: A team's total runs for an innings (an aggregate) collapses every ball into one number, but a batsman's running strike rate shown ball-by-ball throughout the innings, without discarding any single delivery, is what a window function computes.

PARTITION BY, ORDER BY, and the Frame Clause

Inside OVER(), PARTITION BY divides rows into independent groups the window function is calculated within, analogous to GROUP BY but without collapsing rows; ORDER BY within OVER() defines the sequence used for order-sensitive functions like ROW_NUMBER, LAG, and running sums; and the frame clause (ROWS BETWEEN ... AND ..., or RANGE BETWEEN ...) further restricts the window to a sliding subset of the partition, such as ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a 7-row moving average. Without an explicit frame clause, an ORDER BY inside OVER() defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is why a running SUM() OVER (ORDER BY order_date) behaves as a cumulative total by default.

🏏

Cricket analogy: Splitting a tournament's batting stats by team (PARTITION BY) and then ranking each team's players by strike rate in order (ORDER BY), while a frame clause limiting the calculation to just the last 3 innings mimics a form-based rolling average.

Ranking and Offset Functions

ROW_NUMBER() assigns a strictly increasing, unique integer per row within its partition regardless of ties; RANK() assigns the same rank to tied rows but then skips ranks (1, 2, 2, 4); DENSE_RANK() also ties equally but leaves no gaps (1, 2, 2, 3). LAG(column, n) and LEAD(column, n) read a value from n rows behind or ahead within the ordered partition without needing a self-join, which makes them the standard way to compute period-over-period differences, such as this month's revenue minus last month's, directly in a single SELECT.

🏏

Cricket analogy: In a batting averages table, ROW_NUMBER gives every player a unique slot even with tied averages, RANK gives two tied players both '2nd' then skips to '4th', and DENSE_RANK gives both '2nd' then continues at '3rd' with no gap, exactly matching how different leaderboard conventions handle ties.

sql

-- Running total per customer, ranking within each region, and month-over-month change
SELECT
  customer_id,
  region,
  order_date,
  total_amount,
  SUM(total_amount) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS running_total,
  RANK() OVER (
    PARTITION BY region ORDER BY total_amount DESC
  ) AS rank_in_region,
  LAG(total_amount, 1) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS previous_order_amount,
  AVG(total_amount) OVER (
    PARTITION BY customer_id ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS trailing_3_order_avg
FROM orders
ORDER BY customer_id, order_date;

Window functions execute after WHERE, GROUP BY, and HAVING but before the final ORDER BY and LIMIT, which is why you cannot reference a window function's alias directly in the same query's WHERE clause; you must wrap the query in a subquery or CTE and filter in the outer layer instead.

Practical Patterns and Performance

A common pattern is deduplication: SELECT DISTINCT ON, or ROW_NUMBER() OVER (PARTITION BY dedup_key ORDER BY updated_at DESC) filtered to row_number = 1 in an outer query, picks the most recent row per key without a self-join or correlated subquery. Performance-wise, PostgreSQL computes each distinct OVER() specification's window separately unless the specifications are textually identical, so repeating the same PARTITION BY/ORDER BY across many window functions in one query is free to write but still costs one sort per distinct window specification, meaning consolidating to a shared named WINDOW clause (WINDOW w AS (PARTITION BY ... ORDER BY ...)) is mainly a readability win, not a performance one, since the planner already reuses the same sort where specifications match exactly.

🏏

Cricket analogy: Picking only the most recent net-practice score per player from a season's worth of logged sessions, discarding older entries, is exactly the ROW_NUMBER-partition-by-player-order-by-date-desc deduplication pattern used to get the latest row per key.

You cannot reference a window function directly inside the WHERE or HAVING clause of the same SELECT, since window functions are evaluated after those clauses. Attempting SELECT * FROM orders WHERE ROW_NUMBER() OVER (...) = 1 raises a syntax/semantic error; wrap the query in a subquery or CTE and filter with WHERE in the outer query instead.

Window functions compute over a set of related rows via OVER() while preserving every individual row in the output, unlike GROUP BY aggregates.
PARTITION BY groups rows for independent calculation; ORDER BY within OVER() sets the sequence for order-sensitive functions.
The frame clause (ROWS/RANGE BETWEEN) restricts calculation to a sliding subset, enabling moving averages and trailing sums.
ROW_NUMBER, RANK, and DENSE_RANK differ specifically in how they handle tied rows.
LAG and LEAD fetch values from neighboring rows in the ordered partition without a self-join, ideal for period-over-period comparisons.
Window function results cannot be filtered directly in the same query's WHERE/HAVING; use an outer query or CTE instead.
ROW_NUMBER() OVER (PARTITION BY key ORDER BY recency DESC) filtered to 1 is the standard 'latest row per key' deduplication pattern.

Practice what you learned

1. What is the key structural difference between a window function and a GROUP BY aggregate?

2. For tied values, how does RANK() differ from DENSE_RANK()?

3. Why can't you write WHERE ROW_NUMBER() OVER (...) = 1 directly in a SELECT's WHERE clause?

4. Which pair of functions lets you compare a row's value to a value from a preceding or following row in the same ordered partition without a self-join?

5. What frame clause would you use to compute a 7-row moving average ending at the current row?

Was this page helpful?

Common Table Expressions