# Running Totals, Moving Averages and LAG — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/w-running

> Frames and offsets.

## 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.

```sql
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.

**Quiz:** Which function returns the previous row's value in the window order?

- [ ] NTILE
- [ ] LEAD
- [ ] FIRST_VALUE
- [x] LAG

*Answer:* LAG. LEAD looks forward.