Why window functions matter

Early in my career I spent hours writing self‑joins just to compute a running total or a moving average. The queries were fragile, hard to read, and they slowed down once the table grew past a few hundred thousand rows. When I discovered window functions, the same logic collapsed into a single, declarative statement that the optimizer could execute in one pass.

The scenario: session‑level metrics

Imagine a SaaS product that logs every user click in a table called events. Each row carries a user_id, a session_id, a timestamp, and a numeric value (for example, revenue or time spent). The product team wants a dashboard that shows, for each session, the cumulative revenue up to each event and the 5‑event moving average of value. This is a classic use case for window functions.

Naïve approach and its pain points

A typical first attempt looks like this:

SELECT e1.*,
       (SELECT SUM(e2.value) FROM events e2
        WHERE e2.session_id = e1.session_id
          AND e2.event_time <= e1.event_time) AS running_total,
       (SELECT AVG(e3.value) FROM events e3
        WHERE e3.session_id = e1.session_id
          AND e3.event_time BETWEEN e1.event_time - INTERVAL '4 events' AND e1.event_time) AS moving_avg
FROM events e1
ORDER BY e1.session_id, e1.event_time;

Two correlated sub‑queries per row mean the planner must scan the table repeatedly. On a million‑row table the query can take minutes, and the syntax makes it easy to introduce off‑by‑one errors.

Refactored query using window functions

With window functions the same result is expressed in a single pass:

WITH ordered AS (
    SELECT
        user_id,
        session_id,
        event_time,
        value,
        ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_time) AS rn
    FROM events
)
SELECT
    user_id,
    session_id,
    event_time,
    value,
    SUM(value) OVER (PARTITION BY session_id ORDER BY event_time
                     ROWS UNBOUNDED PRECEDING) AS running_total,
    AVG(value) OVER (PARTITION BY session_id ORDER BY event_time
                     ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg_5
FROM ordered
ORDER BY session_id, event_time;

Breaking down the pieces

  • CTE ordered – adds a deterministic row number per session; useful if you later need to filter the first N events.
  • SUM(...) OVER (PARTITION BY session_id ORDER BY event_time ROWS UNBOUNDED PRECEDING) – computes the cumulative sum from the first row of the partition up to the current row.
  • AVG(...) OVER (PARTITION BY session_id ORDER BY event_time ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) – a sliding window of five rows (the current row plus the four preceding ones).
  • ROWS vs RANGE – ROWS counts physical rows, which is what we want for a “last 5 events” metric. RANGE would use the timestamp value and could produce a variable‑size window.

Performance considerations

Because the window functions are evaluated after the single sort on (session_id, event_time), PostgreSQL can stream the data through a sort node and then apply the aggregates in memory. No repeated scans, no temporary tables. In my production environment the refactored query drops from ~45 seconds to under 2 seconds on a 5‑million‑row dataset, and the plan stays stable as data grows.

When to reach for this pattern

Any time you need running totals, moving averages, rank, lead/lag, or percentiles inside a partition, window functions are the idiomatic choice. They keep the SQL declarative, let the optimizer do its job, and make the intent obvious to the next developer who reads the code. I now reach for them before I even think about a self‑join.