Running Totals and Moving Averages in BigQuery: SUM() OVER and AVG() OVER

A regular GROUP BY answers questions by collapsing many rows into one summary row. That is perfect for a grand total, but it throws away the detail, and a lot of the most useful analysis needs both the detail and the running summary at once. How did revenue accumulate day by day? What is the smoothed seven-day trend behind a noisy daily metric? Window functions answer exactly these, computing an aggregate for every row while keeping the row itself. This guide covers the two you will reach for most, SUM() OVER and AVG() OVER, which between them give you running totals, cumulative averages, and moving averages.

The OVER clause, piece by piece

Every window function attaches an OVER clause that defines the set of rows, the window, each calculation looks at. Three parts shape that window. PARTITION BY splits the data into independent groups, so the calculation restarts for each one. ORDER BY sets the order within a window, which is what makes cumulative calculations meaningful. And an optional ROWS BETWEEN clause defines the frame explicitly, naming exactly which rows around the current one are included.

SUM(column) OVER (
  PARTITION BY group_column
  ORDER BY sort_column
  ROWS BETWEEN frame_start AND frame_end
)

Get comfortable with those three knobs and every example below is just a different setting of them.

Running totals with SUM() OVER

The simplest and most common use is a running total. Order the rows by date and sum, and each row shows the cumulative figure up to and including that date.

SELECT
  date,
  sales,
  SUM(sales) OVER (ORDER BY date) AS running_total
FROM `myproject.finance.daily_sales`
ORDER BY date

Each row keeps its own daily sales and gains the total so far. This is the natural way to produce year-to-date revenue or cumulative user growth, where you want to see the climb, not just the endpoint.

Cumulative averages with AVG() OVER

Swap SUM for AVG with the same ordering, and instead of a running total you get a running average, the mean of every value from the first row through the current one.

SELECT
  date,
  sales,
  AVG(sales) OVER (ORDER BY date) AS cumulative_avg
FROM `myproject.finance.daily_sales`
ORDER BY date

This is useful for a long-run baseline that absorbs each new day, such as the average session duration to date.

Moving averages and the default-frame trap

A moving average is different from a cumulative one. Rather than averaging everything so far, it averages a fixed window of recent rows, which smooths out short-term noise. To get it you must define the frame explicitly, taking the current row and a set number of preceding ones.

SELECT
  date,
  sales,
  AVG(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS three_day_avg
FROM `myproject.finance.daily_sales`
ORDER BY date

This averages the current day and the two before it, a three-day moving average. The crucial thing to understand here is why the frame is mandatory. When you write ORDER BY without a ROWS clause, the window does not default to a moving frame, it defaults to everything from the start up to the current row, which gives a cumulative result. So a cumulative average and a moving average use almost the same SQL, and the only thing standing between them is whether you specified the frame. Forgetting it is the most common reason a moving average comes out looking like a running one. For a seven-day moving average you would simply widen the frame to six preceding rows and the current one.

Restarting per group with PARTITION BY

So far the window has spanned the whole table. Adding PARTITION BY confines it to each group and restarts the calculation at every group boundary, which is how you get a running total per category rather than across all categories mixed together.

SELECT
  category,
  date,
  sales,
  SUM(sales) OVER (PARTITION BY category ORDER BY date) AS category_running_total,
  AVG(sales) OVER (PARTITION BY category ORDER BY date) AS category_cumulative_avg
FROM `myproject.finance.daily_sales`
ORDER BY category, date

Each category now gets its own independent running total and cumulative average, all in a single query, with no need for separate queries per group.

Reading window frames

It helps to have a mental library of the common frames. Unbounded preceding to the current row means everything from the start up to now, which is the cumulative pattern for running totals and to-date averages. A frame of one preceding and one following spans the previous, current, and next row, a small centred moving average. Two preceding and the current row, as above, is a short trailing moving average, and widening the preceding count gives you longer windows like a six-preceding seven-day average. Choosing the frame is choosing the question: cumulative or moving, trailing or centred, short or long.

A real example: monthly cumulative revenue

Window functions compose nicely with date functions, which lets you express something genuinely useful, a running total that resets each month per product. The trick is to partition by both the product and the truncated month, so the cumulative sum starts fresh at the beginning of every month for every product.

SELECT
  product_id,
  DATE_TRUNC(date, MONTH) AS month,
  revenue,
  SUM(revenue) OVER (
    PARTITION BY product_id, DATE_TRUNC(date, MONTH)
    ORDER BY date
  ) AS cumulative_monthly_revenue
FROM `myproject.sales.sales`
ORDER BY product_id, date

Because the partition includes the month, the running total climbs through each month and then resets at the start of the next, giving a clean month-to-date figure per product on every row.

Why not just use GROUP BY

The honest comparison is this. GROUP BY is excellent and usually faster when you only want the summarised result and do not need the underlying rows. Window functions are what you reach for when you need per-row metrics, cumulative or moving calculations, or both the detail and the summary side by side, which GROUP BY can only approximate with awkward subqueries. The cost is that window functions do a little more work than a plain aggregation, so the rule of thumb is to use GROUP BY when you want the rolled-up answer and window functions when you want the rolled-up answer attached to every row.

Keeping window queries fast and correct

A few habits matter. Partition appropriately, because smaller windows are cheaper to compute than one window spanning a giant table. Filter early so the window operates on fewer rows, and select only the columns you need, since BigQuery still charges by the data scanned. Be deliberate about the frame, specifying ROWS BETWEEN whenever you want a moving window rather than letting it silently default to cumulative. And check your ORDER BY column carefully, because the whole calculation depends on rows being in the right logical order, usually by date or another monotonic key. Get the partition, the order, and the frame right, and SUM() OVER and AVG() OVER turn running totals and moving averages, the staples of trend analysis, into a single readable line of SQL.

See you soon.