Skip to content
Tutorials Center
My learning

Topics

Lesson 51 of 125 ยท Window Functions

Running Totals and Moving Averages

Compute running totals, cumulative counts, moving averages and running maximums in SQL with SUM() OVER and ROWS frames, and avoid the classic tie trap.

A running total (or cumulative sum) adds each value to the sum of all values before it: revenue so far this year, the balance of a bank account after each transaction, the number of users who have signed up to date. A moving average smooths noisy data by averaging each value with its neighbors, for example a 3-month or 7-day average.

Before window functions, both needed slow correlated subqueries or application code. Now they're a single line: an aggregate with ORDER BY inside OVER, plus an optional frame that says which rows to include. This lesson covers the patterns you'll use in real reports, and one trap with ties that catches almost everyone once.

The examples use the shop database. Revenue means the sum of quantity * unit_price over order items, ignoring cancelled orders.

A first running total

Start with monthly revenue, then add a running total. The CTE computes one row per month; the window adds them up in month order:

SQL Running total of monthly revenue ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The running total climbs from 2,147.30 in January to 26,515.90 in December, which is the whole year's revenue. Each row's revenue_to_date is its own revenue plus the previous row's total.

SUM(revenue) OVER (ORDER BY month) means: for each row, sum revenue over all rows whose month comes at or before this row's month. The ORDER BY inside OVER creates that "up to here" window, as you saw in Window Functions.

Round at the end. Running sums of decimal values like 34.90 can show floating-point noise such as 5638.899999999999. Sum the raw values and apply ROUND() only to the displayed result, as above, so rounding errors don't accumulate.

Running totals per group

Add PARTITION BY and each group gets its own running total that restarts at zero. Here every order of a few customers shows how much that customer has spent so far:

SQL Lifetime spend per customer, order by order ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

order_number is a running count: 1 for a customer's first order, 2 for the second, and so on. Running counts answer questions like "how much did customers spend on their first order compared with their third?"

Totals that reset each period

"Quarter-to-date" and "year-to-date" figures are running totals that start over at the beginning of each period. Partition by the period and order by the date inside it. SQLite has no quarter format code, so the quarter is computed from the month number:

SQL Quarter-to-date revenue ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The running total restarts in April, July and October. With several years of data, partition by year and quarter together (PARTITION BY strftime('%Y', ...), quarter), or simply by strftime('%Y', ...) for year-to-date.

The tie trap: RANGE vs ROWS

Look at what happens when the ORDER BY value repeats. Three orders were placed on February 27, 2025. Order the running total by date only and watch those rows:

SQL Ties share one running total ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In the default_frame column, all three February 27 orders show the same running total, which already includes all three of them. The rows_frame column grows one order at a time.

The reason is the default frame. When a window has ORDER BY but no explicit frame, the standard says it means:

SQL
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

RANGE treats rows with equal ORDER BY values as peers, and "current row" includes all of the current row's peers. ROWS counts physical rows, so it stops exactly at the current one.

Both are correct; they answer different questions. "Revenue up to and including this date" wants RANGE (ties together). "Balance after this transaction" wants ROWS. Two habits keep you safe:

  • Make the ORDER BY unique when you mean "row by row", for example ORDER BY ordered_at, order_id.
  • Or write the frame explicitly: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Window Frames in Depth explains ROWS, RANGE and GROUPS fully.

Moving averages

A moving average replaces the "since the beginning" window with a sliding one of fixed size. The frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW covers the current row and the two before it, a 3-row window that moves down the result:

SQL 3-month moving average ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Revenue swings from 4,514 in March down to 213 in April, but the full-window moving average stays between about 1,786 and 2,780. Two details:

  • The first rows have smaller windows. January has no previous months, so its "3-month" average is just January. months_in_window makes that visible, and the CASE returns NULL until a full window exists. Decide which behavior your report needs.
  • WINDOW w AS (...) names the window once so three functions can share it. It's standard SQL, supported by SQLite, PostgreSQL, MySQL 8 and SQL Server 2022.

You can center the window too: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING averages each month with the month before and after it. Centered averages are common in charts of historical data, but they "look ahead", so don't use them for forecasting or alerts.

Missing periods distort row-based windows. ROWS BETWEEN 2 PRECEDING means "two rows back", not "two months back". If a month has no orders, it has no row, and the window silently stretches over a longer time span. Fill gaps with a generated calendar first (Recursive CTEs shows how) or use a value-based RANGE frame.

Running maximum and minimum

MAX and MIN work as running aggregates too. A running maximum is a "high-water mark": the record so far. This compares each month's revenue with the best month up to that point:

SQL Best month so far ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

January sets the first record by definition, then March beats it, and no later month comes close to March's 4,514. The same pattern computes a stock's all-time high or a "drawdown" (how far the current value is below the running maximum).

Cumulative percentages: the Pareto check

Combine a running total with a grand total to get a cumulative percentage. A common question is "which products make up 80% of revenue?" (the Pareto principle):

SQL Products behind 80 percent of revenue ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The WHERE keeps every product that starts before the 80% mark (running_revenue - revenue is the total of the products before it). In this shop, 12 of the 26 products bring in 80% of revenue: the first 11 reach 79.5%, and the fountain pen carries the total past the mark. The ultralight tent and the adjustable dumbbells alone earn more than a quarter of everything.

The old way: correlated subqueries

You will still meet running totals written without window functions, especially in old MySQL 5.7 code:

SQL
-- Works everywhere, but re-sums the earlier rows for every row
SELECT
  m.month,
  (SELECT SUM(m2.revenue) FROM monthly AS m2 WHERE m2.month <= m.month) AS revenue_to_date
FROM monthly AS m;

For n rows, this sums roughly nยฒ/2 values, which gets slow fast: 10,000 rows mean about 50 million additions. A window function reads the sorted rows once and keeps a running sum. Prefer windows whenever your database supports them (SQLite 3.25+, MySQL 8+, every current PostgreSQL and SQL Server).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does SUM(x) OVER (ORDER BY day) compute when no two rows share a day?

    With ORDER BY and no explicit frame, the window runs from the start of the partition to the current row (and its peers), which is a running total.
  2. 2. Three rows share the same ORDER BY value. With the default frame, what running totals do they get?

    The default frame is RANGE ... CURRENT ROW, which treats tied rows as peers and includes all of them. Use ROWS or a unique ORDER BY to go row by row.
  3. 3. Which frame gives a 7-row moving average ending at the current row?

    Six previous rows plus the current row make seven. 7 PRECEDING would give eight rows; 3 PRECEDING and 3 FOLLOWING is a centered seven-row window.
  4. 4. What does adding PARTITION BY customer_id to a running total do?

    Each partition is an independent window, so the cumulative sum starts again at each customer's first row.

Summary

  • A running total is SUM(x) OVER (ORDER BY ...); add PARTITION BY to restart it per group.
  • COUNT(*) OVER (ORDER BY ...) is a running count, and MAX/MIN give running records.
  • The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so tied rows share a total; use ROWS or a unique ORDER BY for row-by-row results.
  • A moving average uses a sliding frame such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW; watch the shorter windows at the start and gaps in the data.
  • Cumulative percentages (running total divided by SUM(x) OVER ()) answer Pareto questions.
  • Window functions replace slow correlated-subquery running totals.

Next: LAG, LEAD and Comparing Rows, where each row looks at its previous and next rows to compute changes.