Calculating Running Totals & Moving Averages in SQL

Data analysis is rarely about looking at a single row in isolation. Most business questions involve context:

• "How does today's revenue compare to yesterday?"
• "What is our cumulative growth this month?"
• "What is the moving average trend?"

Before SQLite 3.25 (2018), this required painful self-joins. Now, we have Window Functions.

A Real Business Scenario: Finance Wants Daily Revenue and Month-to-Date Totals

Imagine a finance dashboard that shows:

• daily store revenue
• cumulative month-to-date revenue
• a smoothed traffic trend for operations reviews

Those metrics are related, but they answer different questions:

• "What happened today?"
• "How much have we accumulated so far?"
• "Is the trend improving once daily noise is smoothed out?"

Window functions are valuable here because they let you keep the original daily row while adding cumulative or rolling context beside it. That is exactly what reporting teams need when they want a chart and a table to agree on the same grain.

The Window: OVER()

The OVER clause defines a "window" of rows surrounding the current row.

Cumulative Sum (Running Total)

To calculate a running total, we sum up everything from the start until the current row.

SELECT 
  date, 
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM sales;

A Common Error Example: Wrong Ordering, Wrong Metric

Running totals are only as trustworthy as the order inside OVER (...).

For example, this looks innocent:

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY amount) AS running_total
FROM sales;

But it does not produce a chronological running total. It accumulates rows by amount size, not by date. The query still runs, yet the metric answers the wrong question.

That is the core risk with window functions: most mistakes are logical, not syntactic. If the ORDER BY inside the window does not match the business timeline, the final chart can look polished and still be wrong.

Interactive Example: Revenue Growth

Let's track the revenue of two different stores over time. We want to see:

1. Daily Revenue: What they made that day.
2. Cumulative Revenue: Total made since the beginning.

Understanding PARTITION BY

Notice PARTITION BY store_name above?

This tells SQL to reset the running total whenever it encounters a new store. Without it, Store B would start with Store A's total added to it!

Boundary and Performance Notes

Window functions are often cleaner than self-joins, but they still require careful framing and ordering.

• If your timestamps are not unique, add a deterministic tiebreaker in ORDER BY so the running metric does not depend on arbitrary row order.
• If you want a true time-based rolling window, ROWS BETWEEN is not always enough when dates are missing. It counts rows, not elapsed calendar time.
• Large partitions can require sorting substantial amounts of data, so indexes on partition and ordering columns still matter.
• Pre-aggregating to the right grain first is often essential. A running total over raw event rows is different from a running total over daily revenue totals.

The fastest way to get an incorrect metric is to skip the "what should one row represent before the window runs?" question.

Moving Averages

Business data is noisy. A "7-Day Moving Average" smooths out the spikes to show the true trend.

We use the ROWS BETWEEN frame clause:

AVG(amount) OVER (
  ORDER BY date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- 3-day average (2 days before + today)
)

When NOT to Use a Running Total or Moving Average

These metrics are useful, but they are not always the right way to tell the story.

Avoid defaulting to them when:

• the audience needs period totals, not cumulative values
• the business question depends on calendar boundaries that require resets not shown in the query
• the moving average hides spikes that are operationally important, such as incidents or fraud bursts

A smoothed line is easier to look at, but sometimes the spikes are exactly what the team needs to investigate.

Official References

• PostgreSQL window function documentation for formal semantics of running and ranking-style window calculations.
• SQLite window function documentation for SQLite support, frame rules, and syntax details.
• PostgreSQL table expressions and window processing order for a readable explanation of how window functions fit into query evaluation.

Conclusion

Window functions like SUM() OVER and AVG() OVER are superpowers for reporting.

• ORDER BY inside OVER defines the sequence.
• PARTITION BY defines where to restart the calculation.
• ROWS BETWEEN defines the window size for moving metrics.
• The row grain must be correct before the window runs, or the cumulative metric will look precise but answer the wrong question.

Related Articles

• Understanding Window Functions: A Practical Guide for the broader model behind SUM() OVER and other analytical window patterns.
• Mastering SQL LEAD and LAG Functions for Row Comparisons for the neighboring-row comparison pattern that often sits next to running totals.
• Time Series Analysis with SQL: A Practical Guide for the reporting context where cumulative metrics and smoothing are most useful.