Window Functions — Fundamentals

TL;DR

A window function computes an aggregate (sum, average, rank, etc.) across a “window” of rows related to the current row, without collapsing the rows the way GROUP BY does. You get the original row plus a new column containing the aggregate.

SELECT user_id, placed, total,
       SUM(total) OVER (PARTITION BY user_id ORDER BY placed) AS running_total
FROM   orders;

This returns one row per order (not one per user) and tags each row with the user’s running spend up to and including that order. GROUP BY can’t do that — once you group by user_id, you lose placed and total at row granularity.

The OVER (…) clause has three parts:

• PARTITION BY — split rows into independent groups. Computation restarts at each partition. Optional; without it, the whole result is one window.
• ORDER BY — order rows within each partition. Required for ranking and frame-aware aggregates.
• Frame (ROWS/RANGE BETWEEN … AND …) — which subset of the ordered partition the function sees. Defaults vary; the default for an ordered window is “from start of partition to current row”.

The picture in your head

GROUP BY is a meat grinder: many rows in, one row out per group. Window functions are a sliding spotlight: every input row becomes an output row, and for each one a spotlight illuminates a configurable subset of peer rows (by partition + frame), over which the aggregate runs.

That’s the whole idea. Everything else is syntax.

A worked example

CREATE TABLE orders (
    id int, user_id int, placed date, total numeric
);
INSERT INTO orders VALUES
  (1, 1, '2026-04-01', 10),
  (2, 1, '2026-04-05', 20),
  (3, 1, '2026-04-10', 30),
  (4, 2, '2026-04-02', 50),
  (5, 2, '2026-04-08', 25);

Without window functions — the GROUP BY way

SELECT user_id, SUM(total) FROM orders GROUP BY user_id;

+---------+------------+
| user_id | sum(total) |
+---------+------------+
|    1    |     60     |
|    2    |     75     |
+---------+------------+

Two rows. We’ve lost id, placed, and per-row total.

With a window — running totals per user

SELECT id, user_id, placed, total,
       SUM(total) OVER (PARTITION BY user_id ORDER BY placed) AS running_user_total
FROM   orders
ORDER  BY user_id, placed;

+----+---------+------------+-------+--------------------+
| id | user_id | placed     | total | running_user_total |
+----+---------+------------+-------+--------------------+
| 1  |    1    | 2026-04-01 |  10   |         10         |
| 2  |    1    | 2026-04-05 |  20   |         30         |
| 3  |    1    | 2026-04-10 |  30   |         60         |
| 4  |    2    | 2026-04-02 |  50   |         50         |
| 5  |    2    | 2026-04-08 |  25   |         75         |
+----+---------+------------+-------+--------------------+

Five rows in, five rows out. The running total restarts for user 2 (that’s PARTITION BY) and accumulates over time within each user (that’s ORDER BY + the default frame).

Anatomy of OVER

agg_function(args) OVER (
    [PARTITION BY col1, col2, ...]
    [ORDER BY col3 [ASC|DESC] [NULLS FIRST|LAST]]
    [frame_clause]
)

PARTITION BY

Splits the result into independent groups. The function restarts at each new partition. Without PARTITION BY, the entire result set is one window.

-- Sum of all totals, repeated on every row
SUM(total) OVER ()                                     -- one window: all rows

-- Sum per user, repeated on every row of that user
SUM(total) OVER (PARTITION BY user_id)                 -- partitioned, no order

-- Running sum per user, ordered by date
SUM(total) OVER (PARTITION BY user_id ORDER BY placed) -- partitioned + ordered

ORDER BY

Two effects: (1) for ranking functions like ROW_NUMBER, defines the order. (2) for aggregate functions, switches the default frame from “entire partition” to “start of partition through current row” — so adding ORDER BY to a SUM(...) OVER (...) turns it from “total” into “running total”. Subtle and important.

Frame: ROWS vs RANGE

The frame says exactly which rows the function sees. Two flavors:

• ROWS BETWEEN n PRECEDING AND m FOLLOWING — physical row offsets.
• RANGE BETWEEN x PRECEDING AND y FOLLOWING — logical value offsets, inclusive of ties.

-- 7-row trailing average (current row + 6 before)
AVG(total) OVER (
    PARTITION BY user_id
    ORDER BY placed
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

-- 7-day trailing average (a date-aware window)
AVG(total) OVER (
    PARTITION BY user_id
    ORDER BY placed
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
)

RANGE with a numeric/temporal interval is the right tool for time-based rolling windows — “last 7 days” honors actual dates even if days are missing. ROWS counts physical rows regardless of date gaps.

The frame keywords:

Default frame — the gotcha

So adding ORDER BY quietly switches you from “aggregate over the whole partition” to “aggregate from start through current row”. This is what makes SUM(total) OVER (PARTITION BY user_id ORDER BY placed) a running total instead of a per-user total.

If you want partition-level totals with ordering for some other reason, say so explicitly:

SUM(total) OVER (
    PARTITION BY user_id
    ORDER BY placed
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

The two families of window functions

Aggregate windows

Same functions as GROUP BY aggregates, applied to the window:

• SUM, AVG, COUNT, MIN, MAX
• STRING_AGG, ARRAY_AGG, BOOL_AND, BOOL_OR
• STDDEV, VARIANCE, percentiles

SELECT user_id, placed, total,
       AVG(total)        OVER (PARTITION BY user_id) AS user_avg,
       total - AVG(total) OVER (PARTITION BY user_id) AS deviation
FROM   orders;

For each row, attach the user’s average order, and the deviation of this order from that average. Two-line query; the GROUP BY + self-join equivalent is much uglier.

Pure window functions

These exist only in window form (no GROUP BY equivalent):

• Ranking: ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, NTILE — see SQL 202.
• Offset: LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE — see SQL 203.

Naming windows with WINDOW

When you reuse the same OVER definition multiple times, factor it out:

SELECT user_id, placed, total,
       SUM(total)   OVER w AS running_total,
       AVG(total)   OVER w AS running_avg,
       COUNT(total) OVER w AS running_n
FROM   orders
WINDOW w AS (PARTITION BY user_id ORDER BY placed);

Same plan; less typing; less chance of typos diverging.

Common pitfalls

• Forgetting that ORDER BY in OVER changes the default frame. A query that worked as a “user total” suddenly becomes a “running total” the moment you add ORDER BY.
• Using ROWS for time-based windows. “Last 7 events” ≠ “last 7 days”. Use RANGE with an interval if you mean wall-clock time.
• Filtering on a window result in WHERE. Window functions run after WHERE (and even after GROUP BY/HAVING). To filter on ROW_NUMBER() OVER (…) = 1, wrap in a CTE/subquery and filter the outer query.
• Window function in GROUP BY. Not allowed; window functions run after grouping.
• Cost surprise. Each distinct OVER clause adds a sort. Ten differently-partitioned windows = ten sorts. Use WINDOW to share partitions, and check EXPLAIN.
• LAST_VALUE returning the current row instead of the partition’s last row. The default frame is “start through current row”, so LAST_VALUE of an ordered window without a frame override returns the current row. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING explicitly. See SQL 203.

Production patterns for ML

1. Per-row “feature relative to peer group”. Almost every “is this above/below the user’s average / cohort median?” feature is a window function:

SELECT order_id, user_id, total,
       total / AVG(total) OVER (PARTITION BY user_id)              AS x_user_avg,
       total - AVG(total) OVER (PARTITION BY country, day)         AS country_day_dev
FROM   orders;

2. Rolling features for time-series models. 7-day, 30-day, 90-day sums and averages — the bread and butter of demand forecasting and churn prediction:

SELECT user_id, day, daily_spend,
       SUM(daily_spend) OVER (
           PARTITION BY user_id
           ORDER BY day
           RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
       ) AS spend_7d,
       SUM(daily_spend) OVER (
           PARTITION BY user_id
           ORDER BY day
           RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
       ) AS spend_30d
FROM   user_daily_spend;

3. Top-K per group for candidate generation. Pick the most recent N items per user (rec-sys candidate set):

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) AS rn
    FROM   interactions
)
SELECT * FROM ranked WHERE rn <= 50;

Resources

• PostgreSQL — window functions tutorial — postgresql.org/docs
• PostgreSQL — window function reference — postgresql.org/docs
• Mode Analytics — window functions — mode.com/sql-tutorial
• Use The Index, Luke — use-the-index-luke.com — window functions and indexes (PARTITION BY can use indexes if the right one exists).
• PostgreSQL documentation — postgresql.org/docs