Running Totals, Moving Averages and LAG

Window frames

With ORDER BY in the window, aggregates become cumulative: sum(amount) OVER (ORDER BY day) is a running total (the default frame is RANGE UNBOUNDED PRECEDING to CURRENT ROW, which includes peers with the same ordering value). An explicit frame such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives a moving average over three rows. LAG and LEAD read previous or next rows for period-over-period changes.

Running total, 3-row moving average and change, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Each paid order adds to the running total; the moving average uses up to three rows; LAG is empty on the first row.

SELECT ordered_on, amount,
       sum(amount) OVER (ORDER BY ordered_on) AS running_total,
       round(avg(amount) OVER (ORDER BY ordered_on
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3,
       amount - LAG(amount) OVER (ORDER BY ordered_on) AS change_vs_prev
FROM orders WHERE status = 'paid'
ORDER BY ordered_on;

Output:

 ordered_on | amount  | running_total | moving_avg_3 | change_vs_prev 
------------+---------+---------------+--------------+----------------
 2026-01-03 | 1200.00 |       1200.00 |      1200.00 |               
 2026-01-20 |  300.00 |       1500.00 |       750.00 |        -900.00
 2026-02-02 |  450.00 |       1950.00 |       650.00 |         150.00
 2026-02-14 | 2000.00 |       3950.00 |       916.67 |        1550.00
 2026-02-28 |  700.00 |       4650.00 |      1050.00 |       -1300.00
(5 rows)

Use ROWS for exact row counts

RANGE frames group ties together, which surprises people when two rows share a date.

Quick check: Which function returns the previous row's value in the window order?

• NTILE
• LEAD
• FIRST_VALUE
• LAG