Running Totals & Moving Averages — Compute running totals and moving averages (intro) - SQL

SECTION 1

Historical Context & Motivation

Long before SQL gained the ability to express them declaratively, running totals and moving averages were fundamental techniques in statistics, finance, and signal processing. A running total—also called a cumulative sum—tracks the accumulated value of a series as each new observation arrives, while a moving average smooths noisy data by averaging a sliding window of recent values. Before the SQL:2003 standard introduced window functions, database developers had to resort to correlated subqueries, self-joins, or cursor-based procedural code—approaches that were error-prone, hard to read, and often performed poorly on large data sets.

1970s • Early Relational Algebra

Codd's relational model treats rows as unordered sets. Running totals require an explicit ordering, so they sit outside classical relational algebra and must be computed procedurally or via self-joins.

1999 • SQL:1999 — OLAP Extensions Proposed

The SQL:1999 standard introduces OLAP-oriented features such as ROLLUP and CUBE, signaling growing demand for analytical queries. However, true row-level window computations are not yet standardized.

2003 • SQL:2003 — Window Functions Standardized

The OVER clause, PARTITION BY, ORDER BY, and frame specifications (ROWS BETWEEN, RANGE BETWEEN) enter the ISO SQL standard, enabling declarative running totals and moving averages.

2012 • SQL:2011 & Broad Vendor Adoption

PostgreSQL 8.4, Oracle 8i, SQL Server 2012, and later MySQL 8.0 ship mature window function implementations. Running totals become a first-class citizen of everyday SQL.

2020s • Modern Analytics Engines

Columnar engines such as DuckDB, ClickHouse, and BigQuery optimize window frames with vectorized execution, making running totals and moving averages performant even over billions of rows.

The central question this lesson addresses is straightforward yet powerful: how can we compute an aggregate that grows or slides across an ordered sequence of rows, without collapsing those rows into a single group? Window functions answer this by letting every row retain its identity while simultaneously seeing a computed aggregate over a defined frame of neighboring rows.

SECTION 2

Core Principles & Definitions

Before diving into syntax, it is essential to internalize the building blocks that make running totals and moving averages possible within SQL's window function framework. Each concept below maps directly to a clause or keyword you will write in production queries.

OVER Clause

The OVER clause transforms an ordinary aggregate (SUM, AVG, COUNT) into a window function. It signals that the aggregate should be computed across a set of rows related to the current row, rather than collapsing the entire group.

PARTITION BY

Analogous to GROUP BY but for windows. It divides the result set into independent partitions; the running total or moving average resets at each partition boundary (e.g., per customer, per product).

ORDER BY (within OVER)

Defines the logical ordering of rows inside each partition. For a running total, ORDER BY determines which row comes first and therefore which values have been accumulated so far.

Frame Specification

The ROWS BETWEEN or RANGE BETWEEN clause specifies the exact subset of rows—the window frame—included in the aggregate. UNBOUNDED PRECEDING to CURRENT ROW yields a running total; N PRECEDING to CURRENT ROW yields an N-row moving average.

Running Total vs. Moving Average

A running total uses SUM with an ever-expanding frame (from the first row to the current row). A moving average uses AVG with a fixed-width sliding frame (e.g., the current row and the two preceding rows).

✦

KEY TAKEAWAY

Think of a window function like a dashcam mounted on a moving car. The car (current row) drives along a highway (ordered partition). A running total is your trip's odometer—it accumulates every mile since you started. A moving average is your recent fuel economy display, averaging only the last N miles. Both dashcam readings exist per moment in time (per row), yet they summarize a broader context.

SECTION 3

Visual Explanation — How the Window Frame Slides

The diagram below illustrates the fundamental difference between a running total and a 3-row moving average applied to the same ordered sequence of daily revenue values. Notice how the running total's frame always starts at the first row and grows, while the moving average's frame maintains a fixed width and slides forward.

Each row shows which values fall inside the frame. The running total frame (purple) grows from the partition start to the current row, while the moving average frame (cyan) always covers exactly three rows and slides forward.

As the current row advances from Monday to Friday, the running total's frame expands to include every preceding row plus the current one, so its SUM is monotonically non-decreasing (assuming non-negative values). The moving average's frame, by contrast, always includes exactly the current row and its two predecessors. Once Wednesday is reached, the first row (Monday) begins to fall out of the moving average frame as Thursday becomes current. This fixed-width sliding behavior is what smooths short-term volatility and reveals longer-term trends.

SECTION 4

SQL Syntax & Frame Specifications

The SQL expressions for running totals and moving averages follow a consistent template. The key syntactic element is the frame clause within the OVER specification. Understanding the frame clause is critical because the default frame—which many developers overlook—varies between database engines and can silently produce incorrect results.

RUNNING TOTAL

SUM(column) OVER ( PARTITION BY partition_col ORDER BY sort_col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )

PARTITION BY divides the data into independent groups. ORDER BY defines the accumulation direction. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW makes the frame start at the first row of the partition and end at the current row—this is what produces the cumulative effect.

N-ROW MOVING AVERAGE

AVG(column) OVER ( PARTITION BY partition_col ORDER BY sort_col ROWS BETWEEN (N−1) PRECEDING AND CURRENT ROW )

For a 3-row moving average, N = 3, so the frame is ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. The frame always contains at most N rows. At the start of a partition, when fewer than N rows precede the current row, AVG automatically computes over the available rows.

⚠

Default Frame Pitfall

When ORDER BY is present but no explicit frame clause is specified, the SQL standard defines the default frame as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Note RANGE, not ROWS. With RANGE, rows sharing the same ORDER BY value (ties) are all included in the frame together, which can produce unexpected running totals when sort keys are not unique. Always specify an explicit ROWS BETWEEN clause to avoid surprises.

ROWS vs. RANGE vs. GROUPS

ROWS — counts individual physical rows RANGE — includes all rows with the same ORDER BY value GROUPS — counts distinct ORDER BY value groups (SQL:2011)

ROWS gives the most precise control and is recommended for running totals and moving averages unless you intentionally want tie-inclusive behavior. GROUPS is the newest mode and is not universally supported.

SECTION 5

Frame Boundary Options & Classification

SQL window frames are defined by a start boundary and an end boundary. The combination of these boundaries determines whether the aggregate behaves as a running total, a moving average, a moving sum, or even a look-ahead computation. The following diagram classifies the most common frame patterns and maps each to its analytical use case.

Five common frame patterns. Running totals use an expanding frame; moving averages use a sliding frame. The centered moving average looks both backward and forward, making it useful for symmetric smoothing in time-series analysis.

Pattern	Frame Clause	Common Aggregate	Typical Use Case
Running Total	`ROWS UNBOUNDED PRECEDING … CURRENT ROW`	`SUM`	YTD revenue, account balance
Moving Average	`ROWS N PRECEDING … CURRENT ROW`	`AVG`	7-day smoothed metrics, stock price
Rolling Sum	`ROWS N PRECEDING … CURRENT ROW`	`SUM`	30-day active users, trailing revenue
Partition Total	`ROWS UNBOUNDED PRECEDING … UNBOUNDED FOLLOWING`	`SUM`	Percent-of-total calculations
Centered MA	`ROWS N PRECEDING … N FOLLOWING`	`AVG`	Seasonal decomposition

Summary of frame boundary patterns and their analytical applications.

SECTION 6

Worked Example — Daily Revenue Analysis

Suppose we have a table called daily_revenue with columns sale_date, region, and amount. We want to compute, for each region, both a running total and a 3-day moving average of daily revenue.

Running Total & 3-Day Moving Average per Region

1
Step 1 — Identify the base query and ordering

We begin by selecting the relevant columns and establishing the row order. Each region's data will be processed independently via PARTITION BY region, and within each partition, rows are ordered by sale_date ascending.

SELECT sale_date, region, amount FROM daily_revenue ORDER BY region, sale_date;
2
Step 2 — Add the running total column

We apply SUM(amount) with an OVER clause that partitions by region, orders by sale_date, and uses an expanding frame from the first row of the partition to the current row. This guarantees that each row's running total equals the sum of all previous amounts plus the current one within that region.

SUM(amount) OVER ( PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total
3
Step 3 — Add the 3-day moving average column

We apply AVG(amount) with a sliding frame of three rows: the current row and the two preceding rows. On the first day of a partition, only one row is available, so AVG returns that single value. On the second day, AVG is computed over two rows. From the third day onward, the full 3-row average is in effect.

AVG(amount) OVER ( PARTITION BY region ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3d
4
Step 4 — Assemble the complete query

Combining both window expressions into one SELECT statement gives us the original row-level data alongside both computed metrics. No GROUP BY is needed—window functions preserve row granularity.

SELECT sale_date, region, amount, SUM(amount) OVER ( PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total, AVG(amount) OVER ( PARTITION BY region ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3d FROM daily_revenue ORDER BY region, sale_date;
5
Step 5 — Verify with sample data

For region = 'East' with amounts [100, 150, 200, 80, 120], the running total at each row is [100, 250, 450, 530, 650]. The 3-day moving average at each row is [100.00, 125.00, 150.00, 143.33, 133.33]. These results match the visual diagram from Section 3, confirming our query is correct.

Row 5 (Fri): running_total = 650, moving_avg_3d ≈ 133.33 ✓

SECTION 7

Strengths, Limitations & Comparisons

Window functions offer a declarative, set-based approach to running computations, but they are not the only option. Understanding when to prefer window functions over alternatives—and recognizing their limitations—helps you make sound engineering decisions.

Approach	Strengths	Limitations
Window Functions (SUM/AVG OVER)	Declarative, readable, composable with other window functions. Preserves row-level detail. Optimized by modern query planners.	Cannot be used in WHERE or HAVING (must be wrapped in a CTE/subquery). Memory overhead for very large partitions. Not all engines support all frame types.
Correlated Subquery	Works in any SQL dialect, including older engines without window function support. Conceptually explicit.	O(n²) worst-case per partition—executes a subquery for every row. Verbose and error-prone for complex frames.
Self-Join	No window function dependency. Clear join semantics for auditing.	Produces row explosion (cartesian-like growth). Hard to parameterize frame width. Poor performance at scale.
Application-Level Loop	Full language expressiveness. Can handle arbitrarily complex logic.	Transfers all data to the client. Loses database-level parallelism and optimization. Harder to maintain in data pipelines.

Comparison of approaches for computing running totals and moving averages.

✦

KEY TAKEAWAY

Window functions are to running aggregates what hash joins are to equi-joins: the standard, performant default in a modern SQL engine. Reach for a correlated subquery or self-join only when your database predates SQL:2003 support, or when you need the result in a WHERE clause and cannot restructure the query with a CTE.

SECTION 8

Connection to Advanced Window Function Techniques

Running totals and moving averages represent the introductory tier of window function mastery. They naturally lead into a family of more advanced analytical patterns that you will encounter in data engineering, machine learning feature stores, and real-time analytics dashboards. The table below maps each introductory concept to its advanced extension.

This Lesson (Intro)	Advanced Extension	What Changes
Running total (SUM OVER)	Conditional running total (SUM with CASE/FILTER)	Accumulate only rows meeting a condition, e.g., SUM(CASE WHEN status = 'shipped' THEN amount END).
Moving average (AVG OVER)	Exponentially weighted moving average (EWMA)	EWMA gives exponentially decreasing weights to older observations. Not natively supported in SQL but can be approximated with recursive CTEs.
ROWS frame	RANGE frame with interval arithmetic	RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW handles irregular time series where rows are not daily.
Single window expression	Named WINDOW clause (WINDOW w AS …)	Define a reusable window specification once and reference it from multiple functions—reduces duplication and errors.
Running total over ordered data	Sessionization & gap-and-island problems	Running totals combined with LAG/LEAD and conditional resets identify session boundaries in clickstream or event data.

Mapping introductory concepts to their advanced counterparts.

As you progress, pay special attention to how RANGE with temporal intervals solves a problem that ROWS cannot: computing a true calendar-based moving average even when some dates have no rows. In many production data warehouses, dates are densified through a calendar dimension table first, and then the ROWS-based moving average is applied—an architectural pattern worth understanding before you encounter it in a pipeline.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain why a running total requires an ORDER BY inside the OVER clause but a simple partition-wide SUM does not. What would happen if you omitted ORDER BY from a running total query?

Show answer

A running total is inherently order-dependent: it accumulates values from the first row up to the current row, which only makes sense when the rows have a defined sequence. Without ORDER BY, the database treats all rows in the partition as peers, and the default frame becomes the entire partition. As a result, every row would show the same value—the total sum of the partition—rather than a cumulative progression. The ORDER BY clause establishes the sequence that gives the running total its characteristic monotonically growing behavior.

Question 2: BASIC CALCULATION

Given a table orders(order_date DATE, total DECIMAL) with values [(2024-01-01, 50), (2024-01-02, 30), (2024-01-03, 70), (2024-01-04, 20)], write a query that returns each row with a cumulative running total. What is the running total on 2024-01-03?

Show answer

The query is: SELECT order_date, total, SUM(total) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM orders. The running total on 2024-01-03 is 50 + 30 + 70 = 150. At each row, the frame includes all rows from the beginning of the result set (UNBOUNDED PRECEDING) through the current row. The explicit ROWS frame ensures deterministic behavior even if two orders shared the same date.

Question 3: INTERMEDIATE

Using the same orders table, write a query that computes a 3-day moving average of total. What value does the moving average return on 2024-01-02 (the second row), and why?

Show answer

The query is: SELECT order_date, total, AVG(total) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3d FROM orders. On 2024-01-02, only two rows are available in the frame (2024-01-01 and 2024-01-02), so the moving average is (50 + 30) / 2 = 40.00, not divided by 3. The AVG function counts only the rows actually present in the frame. This partial-window behavior is important to understand: at partition edges, the moving average is computed over fewer rows than the specified window width, which can cause the early values to be noisier than expected.

Question 4: APPLIED

A SaaS company tracks daily sign-ups in a table signups(signup_date DATE, plan VARCHAR, count INT). Write a single query that returns, for each plan, the sign-up date, daily count, running total of sign-ups since the plan's first record, and a 7-day moving average of daily sign-ups. Explain the frame choices.

Show answer

SELECT signup_date, plan, count, SUM(count) OVER (PARTITION BY plan ORDER BY signup_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total, AVG(count) OVER (PARTITION BY plan ORDER BY signup_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d FROM signups ORDER BY plan, signup_date. The PARTITION BY plan ensures each plan's metrics are independent. The running total uses UNBOUNDED PRECEDING to accumulate from the first sign-up record onward. The 7-day moving average uses 6 PRECEDING (not 7) because the frame includes the current row, making 6 + 1 = 7 rows total. During the first six days of each plan's data, the moving average is computed over fewer than 7 rows, which is the standard partial-window behavior.

Question 5: CRITICAL THINKING

Suppose your time-series data has gaps—certain dates have no rows. A ROWS-based 7-day moving average will incorrectly include values from more than 7 calendar days ago. Propose two distinct strategies to ensure the moving average covers exactly a 7-calendar-day window despite missing dates. Discuss the tradeoffs of each approach.

Show answer

Strategy 1: Date densification. Left join the fact table against a continuous calendar dimension (generate_series or a date spine table) so that every calendar date has a row, with NULL or 0 for missing days. Then apply the standard ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. This ensures each row offset maps exactly to one calendar day. The tradeoff is that it inflates the row count and requires maintaining a calendar table, but the window function logic remains simple and portable. Strategy 2: Use RANGE with an interval. Write RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW instead of ROWS. The database engine will include all rows whose ORDER BY value falls within the specified interval of the current row's date. This approach handles gaps naturally without densification, but it has caveats: RANGE BETWEEN INTERVAL is not universally supported (PostgreSQL supports it; MySQL 8.0 does not for intervals), and performance characteristics vary by engine. Both strategies are valid in production; the choice depends on your database's feature set and whether downstream consumers expect a row for every date.

SUMMARY

Summary

SQL window functions enable running totals and moving averages by computing aggregates across a defined window frame without collapsing rows. A running total uses SUM with an expanding frame (UNBOUNDED PRECEDING to CURRENT ROW), accumulating all preceding values. A moving average uses AVG with a sliding frame of fixed width (N PRECEDING to CURRENT ROW), smoothing recent values.

The OVER clause converts standard aggregates into window functions, PARTITION BY segments data into independent groups, ORDER BY establishes the logical row sequence, and the ROWS BETWEEN clause precisely controls which rows are included. Always specify an explicit frame to avoid the default RANGE behavior, which groups tied ORDER BY values and can produce unexpected results. These patterns form the foundation for advanced techniques including conditional running totals, EWMA approximations, interval-based RANGE frames, and sessionization queries.