All Articles

Running Totals and Moving Averages in SQL

Window Functions & CTEs
#running-total#moving-average#window-functions#sql-interview#analytics-sql

Why This Pattern Matters

Running totals and moving averages are two of the most common calculations in financial reporting, analytics dashboards, and operational monitoring — cumulative revenue for the year, a 7-day rolling average of daily signups, a smoothed trend line to cut through noisy daily data. Both are built entirely on window functions with frame clauses, and both are frequently requested in interviews specifically to test whether you understand how the default window frame works.

The Sample Data

-- daily_revenue
| order_date | revenue |
|------------|---------|
| 2026-01-01 | 1000    |
| 2026-01-02 | 1200    |
| 2026-01-03 | 900     |
| 2026-01-04 | 1500    |
| 2026-01-05 | 1100    |
| 2026-01-06 | 1300    |
| 2026-01-07 | 950     |

Running Total: The Basic Pattern

SELECT
  order_date,
  revenue,
  SUM(revenue) OVER (ORDER BY order_date) AS running_total
FROM daily_revenue;

-- Result:
-- | order_date | revenue | running_total |
-- |------------|---------|---------------|
-- | 2026-01-01 | 1000    | 1000          |
-- | 2026-01-02 | 1200    | 2200          |
-- | 2026-01-03 | 900     | 3100          |
-- | 2026-01-04 | 1500    | 4600          |

This works with no explicit frame clause because a window function's default frame — when ORDER BY is present but a frame isn't specified — is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. In plain terms: "everything from the start up to and including the current row," which is exactly the definition of a running total.

Running Total Per Group

Add PARTITION BY to reset the running total independently for each group — for example, a running total per product rather than across the whole table.

SELECT
  product, order_date, revenue,
  SUM(revenue) OVER (PARTITION BY product ORDER BY order_date) AS running_total_by_product
FROM sales;

Moving Average: You Need an Explicit Frame

A moving average — the average of the current row plus a fixed number of preceding rows — is where people most often go wrong, because it requires an explicit ROWS BETWEEN frame instead of relying on the default.

SELECT
  order_date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3d
FROM daily_revenue;

-- moving_avg_3d for 2026-01-03 = AVG(1000, 1200, 900) = 1033.33
-- moving_avg_3d for 2026-01-04 = AVG(1200, 900, 1500) = 1200.00

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means "this row plus the 2 rows before it" — a 3-row window. For a 7-day moving average, use 6 PRECEDING (6 prior rows + the current row = 7 total).

Note the first two rows: since there aren't 2 full prior rows available yet at the start of the series, the frame simply uses however many rows actually exist — most databases don't error, they just average over a smaller window near the edges. Decide explicitly whether that's acceptable for your use case, or whether early rows should show NULL until a full window is available (add a CASE guard based on ROW_NUMBER() if so).

Centered Moving Average

Sometimes you want the average centered on the current row — some days before, some days after — rather than only looking backward.

SELECT
  order_date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS centered_moving_avg_3d
FROM daily_revenue;

Centered averages are common in signal-smoothing and scientific contexts, but note they can't be computed for "live" data — the trailing (backward-only) version above is almost always what dashboards use, since it doesn't require future data that hasn't happened yet.

Percent of Running Total / Cumulative Distribution

A frequent follow-up: express the running total as a percentage of the grand total, useful for Pareto-style ("what % of revenue comes from the top N days") analysis.

SELECT
  order_date,
  revenue,
  SUM(revenue) OVER (ORDER BY order_date) AS running_total,
  ROUND(
    100.0 * SUM(revenue) OVER (ORDER BY order_date)
    / SUM(revenue) OVER (), 2
  ) AS pct_of_total
FROM daily_revenue;

SUM(revenue) OVER () with an empty OVER() — no PARTITION BY, no ORDER BY — computes the grand total across the entire result set, repeated on every row, which is exactly what's needed as the denominator here.

Handling Gaps in the Date Series

If a day has zero orders, it simply won't appear as a row at all — which silently skews a "7-day" moving average into effectively averaging over a wider or narrower actual date range than intended. The fix is to generate a complete date spine first (see our recursive CTE guide or use generate_series in PostgreSQL) and LEFT JOIN the real data onto it, coalescing missing revenue to 0, before applying the window function.

WITH date_spine AS (
  SELECT generate_series('2026-01-01'::date, '2026-01-07'::date, interval '1 day')::date AS d
),
filled AS (
  SELECT ds.d AS order_date, COALESCE(dr.revenue, 0) AS revenue
  FROM date_spine ds
  LEFT JOIN daily_revenue dr ON dr.order_date = ds.d
)
SELECT order_date, revenue,
  AVG(revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM filled;

Common Mistakes

  • Forgetting ROWS BETWEEN for a moving average and getting a running total instead — the single most common mistake on this topic, since the default frame silently produces different, plausible-looking-but-wrong numbers.
  • Missing PARTITION BY when totals should reset per group (per product, per customer) — without it, the running total bleeds across every group in the table.
  • Not accounting for missing dates — a moving average silently computed over the wrong actual date range because zero-order days don't exist as rows.
  • Confusing ROWS and RANGE frames when the ORDER BY column has duplicate values (e.g. multiple orders with the identical timestamp) — RANGE treats ties as a single frame step, which can pull in more rows than expected. Use ROWS for predictable, count-based windows.

Common Interview Questions

  1. Write a query for a running total of revenue by date. The basic SUM() OVER (ORDER BY ...) pattern shown above.
  2. Write a query for a 7-day moving average. AVG() OVER (ORDER BY ... ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) — and mention the date-gap caveat unprompted, since it's a strong signal of real experience.
  3. Why does a running total work without specifying ROWS BETWEEN, but a moving average doesn't? Explain the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) versus the need for an explicit bounded frame for a fixed-size window.
  4. How would you calculate what percentage each day contributes to the month's total? revenue / SUM(revenue) OVER () as shown in the cumulative distribution section.

Frequently Asked Questions

Is a running total the same thing as a cumulative sum?

Yes, those terms are used interchangeably — both describe a total that accumulates as you move through an ordered sequence of rows.

Can I compute a moving average without window functions?

Technically yes, with a correlated subquery or self join averaging a date range per row, but it's significantly more verbose and typically slower — window functions with an explicit frame are the standard modern approach on any database released in the last several years.

Practice This Pattern

Running totals and moving averages are foundational to almost every analytics dashboard — get comfortable with the frame clause and the rest follows naturally. Try it hands-on with our SQL practice questions, or apply it to real time-series data in our case studies. For the underlying window function mechanics, see our complete guide to window functions.

Back to All Articles