SQL window functions for real reports: LAG gaps, running totals and frames

Our analytics backlog had a familiar pattern: a dashboard question arrives ("how many days between a customer's orders?", "what is revenue month to date?"), someone writes a correlated subquery or pulls rows into Python and loops, and the query takes minutes once the table grows. Almost all of those questions are window function questions.

This guide walks through the window functions we use most for real reports, with one running example: an orders table in PostgreSQL 17. By the end you should be able to compute gaps between events, running and moving totals, period-over-period change, and you will know the frame clause default that silently produces wrong numbers.

The running example

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    ordered_at  timestamptz NOT NULL,
    amount      numeric(10,2) NOT NULL
);
CREATE INDEX orders_customer_time_idx ON orders (customer_id, ordered_at);

A window function computes a value for each row using a set of related rows, without collapsing them like GROUP BY does. The shape is always the same:

function(args) OVER (
    PARTITION BY ...   -- which rows belong together
    ORDER BY ...       -- order inside the partition
    ROWS|RANGE|GROUPS BETWEEN ... AND ...  -- the frame, optional
)

Window functions run after WHERE, GROUP BY and HAVING, and before the final ORDER BY and LIMIT. That ordering explains a common surprise: you cannot filter on a window result in the same WHERE. Wrap the query in a CTE or subquery and filter outside.

LAG and LEAD: gaps between events

"Days since the previous order" for each order is the classic LAG use case:

SELECT customer_id, id, ordered_at,
       ordered_at - LAG(ordered_at) OVER (
           PARTITION BY customer_id ORDER BY ordered_at
       ) AS since_previous
FROM orders;

The first order of each customer gets NULL, because there is no previous row. LAG takes an optional offset and default, so LAG(amount, 1, 0) returns 0 instead of NULL.

From there, churn-style questions become one more step. Customers whose gap ever exceeded 90 days:

WITH gaps AS (
    SELECT customer_id,
           ordered_at - LAG(ordered_at) OVER (
               PARTITION BY customer_id ORDER BY ordered_at
           ) AS gap
    FROM orders
)
SELECT customer_id, max(gap) AS longest_gap
FROM gaps
GROUP BY customer_id
HAVING max(gap) > interval '90 days';

LEAD looks forward instead. "Did this customer order again within 30 days?" is LEAD(ordered_at) OVER (...) - ordered_at <= interval '30 days'.

Sessionising events

The same idea splits a stream of events into sessions. Mark a row as a new session when the gap exceeds a threshold, then run a cumulative sum over that flag:

WITH flagged AS (
    SELECT customer_id, ordered_at,
           CASE WHEN ordered_at - LAG(ordered_at) OVER w > interval '30 minutes'
                  OR LAG(ordered_at) OVER w IS NULL
                THEN 1 ELSE 0 END AS new_session
    FROM orders
    WINDOW w AS (PARTITION BY customer_id ORDER BY ordered_at)
)
SELECT customer_id, ordered_at,
       SUM(new_session) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS session_no
FROM flagged;

The WINDOW w AS (...) clause names a window once so you can reuse it. It keeps long reports readable and makes sure every function uses the same partition and order.

Running totals

Revenue to date per day is an aggregate with ORDER BY inside OVER:

WITH daily AS (
    SELECT date_trunc('day', ordered_at)::date AS day, sum(amount) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day) AS revenue_to_date,
       SUM(revenue) OVER (PARTITION BY date_trunc('month', day) ORDER BY day) AS month_to_date
FROM daily
ORDER BY day;

Two things are happening. The aggregate in daily collapses orders to one row per day. The window then runs on those daily rows. Mixing both in one SELECT is allowed (SUM(SUM(amount)) OVER (...)), but the CTE version is easier to read and debug.

The frame default that skews numbers

This is the part that bites people. When you write ORDER BY inside OVER and no frame clause, the default frame is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

RANGE means "all rows whose sort value is less than or equal to the current row's value". Rows with the same sort value are peers, and they all get the same result. Run a running total directly on the orders table, ordered by a timestamp that sometimes repeats (bulk imports, second-level precision), and peers all show the total including each other:

SELECT id, ordered_at, amount,
       SUM(amount) OVER (ORDER BY ordered_at) AS running_range,
       SUM(amount) OVER (ORDER BY ordered_at
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows
FROM orders
WHERE customer_id = 42;

 id  |       ordered_at       | amount | running_range | running_rows
-----+------------------------+--------+---------------+-------------
 901 | 2024-12-01 10:00:00+00 |  20.00 |         20.00 |        20.00
 902 | 2024-12-02 09:15:00+00 |  15.00 |         50.00 |        35.00
 903 | 2024-12-02 09:15:00+00 |  15.00 |         50.00 |        50.00
 904 | 2024-12-05 18:40:00+00 |  30.00 |         80.00 |        80.00

Neither is "wrong", but only one matches what your users expect. If you want a strict row-by-row running total, say ROWS and add a unique column to the order (ORDER BY ordered_at, id) so the order of peers is repeatable.

The same default explains why LAST_VALUE seems broken: with the default frame, the last row of the frame is the current row (or its last peer), not the last row of the partition. Ask for the whole partition explicitly:

LAST_VALUE(amount) OVER (
    PARTITION BY customer_id ORDER BY ordered_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

FIRST_VALUE works with the default because the first row is always in the frame.

Moving averages

A 7-day moving average on daily data is a ROWS frame of 7 rows, as long as every day has a row:

SELECT day, revenue,
       AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7_rows
FROM daily;

Days with no orders have no row, so "6 preceding rows" might cover two weeks. Two fixes. Fill the gaps with generate_series and a left join, so each day exists with revenue 0. Or use a RANGE frame with an interval offset, which PostgreSQL has supported since version 11 and which looks at values, not row counts:

SELECT day, revenue,
       AVG(revenue) OVER (ORDER BY day
                          RANGE BETWEEN interval '6 days' PRECEDING AND CURRENT ROW) AS avg_7_days
FROM daily;

Note the subtle difference: the RANGE version averages over the days that exist in the window, so a missing day lowers the count instead of contributing a zero. For revenue we fill gaps with zeros; for something like "average response time" the RANGE version is what we want.

Period-over-period change

Month-over-month growth combines aggregation and LAG:

WITH monthly AS (
    SELECT date_trunc('month', ordered_at)::date AS month, sum(amount) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev,
       round(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS pct_change
FROM monthly;

NULLIF(..., 0) prevents a division-by-zero error when a month had no revenue. For year-over-year with monthly rows, LAG(revenue, 12) works only if no month is missing; join on month - interval '1 year' when gaps are possible.

Shares and buckets

Two more patterns that appear in almost every report:

SELECT customer_id, sum(amount) AS total,
       round(100.0 * sum(amount) / SUM(sum(amount)) OVER (), 2) AS pct_of_all,
       NTILE(4) OVER (ORDER BY sum(amount) DESC) AS quartile
FROM orders
GROUP BY customer_id;

An empty OVER () means the whole result set is one partition, which gives a grand total next to each row. NTILE(4) splits the ordered rows into four buckets of nearly equal size, useful for "top quartile customers". Ranking functions (ROW_NUMBER, RANK, DENSE_RANK) belong here too, but they have their own tie-handling traps that we cover separately.

Performance notes

• Each distinct PARTITION BY / ORDER BY combination needs its own sort. Reusing one named WINDOW across several functions lets the planner sort once.
• An index matching (partition columns, order columns) can remove the sort entirely; check EXPLAIN for a WindowAgg fed by an Index Scan instead of a Sort.
• Filter early. Put date range conditions in the innermost query so the window processes fewer rows, but remember that filtering removes rows the window might need (the LAG of the first row in your range will be NULL). Pull one extra period and trim it outside.

Common mistakes

Key takeaways

• Use LAG/LEAD for gaps, churn and sessions instead of self-joins.
• SUM(...) OVER (ORDER BY ...) gives running totals; add PARTITION BY for month-to-date style resets.
• The default frame is RANGE ... CURRENT ROW; spell out ROWS when you mean row by row.
• RANGE with interval offsets handles calendar windows over sparse data.
• Name windows with WINDOW w AS (...) to keep reports readable and let the planner share sorts.

References

• PostgreSQL docs, Window Functions tutorial: https://www.postgresql.org/docs/17/tutorial-window.html
• PostgreSQL docs, Window Function Calls (frame clause): https://www.postgresql.org/docs/17/sql-expressions.html
• PostgreSQL docs, Window Functions reference: https://www.postgresql.org/docs/17/functions-window.html