Running Totals & Moving Averages — Compute running totals...

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.

Summary of frame boundary patterns and their analytical applications.

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

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

Step 1 — Identify the base query and orderingWe 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;

Step 2 — Add the running total columnWe 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

Step 3 — Add the 3-day moving average columnWe 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

Step 4 — Assemble the complete queryCombining 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;

Step 5 — Verify with sample dataFor 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.

Comparison of approaches for computing running totals and moving averages.

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.

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

Mapping introductory concepts to their advanced counterparts.

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.

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

PROBLEM 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?

PROBLEM 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?

PROBLEM 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?

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

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

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.