---
title: "Running totals and rolling averages | Oplema"
description: "Learn to calculate a running total and a rolling (moving) average, handle partial windows and choose between row and calendar windows, in SQL and Excel."
url: "https://oplema.com/learn/data-preparation/rolling-windows"
updated: "2026-11-04"
---

Lesson 14 of 15 · Lab

# Running totals and rolling averages

A running total adds each row to everything before it. A rolling average, also called a moving average, averages only the last few rows, which smooths out single spikes. Use a running total for "how much so far", and a rolling average for "how fast lately".

The values 10, 20, 30, 40 in date order
| Value | Running total | Rolling average, 2 rows |
| --- | --- | --- |
| 10 | 10 | 10 |
| 20 | 30 | 15 |
| 30 | 60 | 25 |
| 40 | 100 | 35 |

Don Tremendous at fictional Vandelay Coffee Supply says South led all quarter. South ends ahead by €322, and a total can't test the rest. **Filter** drops the unpriced invoice and **Sort** orders each region by date, because a running total needs both. [Meet Don and his report](https://oplema.com/learn/data-preparation/what-is-data-prep).

South ends the quarter ahead. Did it lead the whole way?

No. A lead of €322 can easily be recent, and the running total shows exactly when it changed.

## Read the running total, then narrow the window

1. **Rolling Calc**, selected in this live flow in [Strata](https://oplema.com/strata), Oplema's browser app for data prep, holds a running sum of `net_eur`, restarted for each `region` by **Partition By**. **Window / offset** is 0, which means every row so far.
2. Read `running` in the preview. North's six rows come first and climb to 5,578. Scroll for South's.
3. Look at the chart under this flow. It draws the running sum over time. Find where the lines cross.
4. Select **Rolling Calc** and set **Window / offset** to 2, then select the block.
5. Read `running`. Each row now adds only itself and the row before, so it shows recent pace instead of the score. The chart stays put, a saved picture of step 2.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

Where the lines should cross

South passes North on September 23, on Initech's second invoice, and finishes at 5,900 to North's 5,578. With a window of 2, the last rows are North 1,710 and South 3,000.

## A total hides when it happened

So you can tell Max Thrust, the founder, that South leads, but only since September 23, and only because of one new customer. Don's "all quarter" isn't in any version of the data, even his own.

To get an average in this flow, change **Operation** to **Running avg** and keep the window. See the [Rolling Calc reference](https://oplema.com/docs/strata/transform/rolling-calc).

## Common rolling window mistakes

- **Partial windows.** The first row of a 2-row window has only one value, so its average is a partial one, 10 in the table above. Turn on **Require full window** to get NULL instead of a thin early figure.
- **Row windows on calendar questions.** Seven rows are seven days only if no day is missing. Fill the gaps, or use a calendar window.
- **No sort.** The total follows the order the rows arrived in, and it still looks plausible.
- **No partition.** Without **Partition By**, North and South add into one line.

## Window functions in SQL, and ranges in Excel

```
SELECT  region,  day ,
  SUM (net)  OVER  ( PARTITION  BY  region  ORDER BY  day )  AS  running_total,
  AVG (net)  OVER  ( PARTITION  BY  region  ORDER BY  day
    ROWS  BETWEEN  1  PRECEDING  AND  CURRENT  ROW )  AS  rolling_avg
FROM  daily
```

`ROWS` counts rows. For a calendar window, PostgreSQL and DuckDB accept `RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW`. In Excel, `=SUM($B$2:B2)` filled down is a running total, because the first cell stays fixed. `=AVERAGE(B2:B3)` filled down is a rolling average, because both ends move.
