Running Totals & Moving Averages

Window functions let a row see its neighbours — the running total, last week's value, the customer's previous order — without collapsing the rows away.

Analytics SQL · Intermediate · ~6 min

In plain English

A spreadsheet formula that can look at the rows above it. Running totals and 'compare to last week' without collapsing anything.

Why it's worth your time

It replaces self-joins that are slower, longer, and much easier to get subtly wrong.

If you remember three things

PARTITION BY resets per group, ORDER BY sequences, the frame picks the neighbours
Default frame is RANGE, so ties aggregate together — use ROWS for a true N-row window
ROW_NUMBER + QUALIFY is the standard latest-row-per-key pattern

Overview

GROUP BY answers questions about groups and destroys the rows. Window functions answer questions about a row in the context of its group and keep every row, which is exactly what time-series analysis needs. Three patterns cover most analyst work: a running total with SUM() OVER (ORDER BY ...), a smoothed trend with AVG() OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), and period-over-period comparison with LAG(). Each is one clause, and each replaces a self-join that would be slower and much easier to get wrong.

In an interview

Window functions compute across a set of rows related to the current row without collapsing them. PARTITION BY resets per group, ORDER BY defines the sequence, and the frame clause defines which neighbouring rows are included. Running totals, 7-day moving averages, and LAG for period-over-period growth are the three patterns that cover most analytics.

Production defaults

Date spine: join to a complete date series before any moving average
Reuse: name the window (WINDOW w AS ...) — fewer sorts, clearer intent
Frames: be explicit even when the default would be right

What breaks

7-day average looks wrong on quiet days — Missing dates mean seven rows isn't seven days. Join a date spine first.
Can't filter on a window function — It isn't computed when WHERE runs. Wrap in a subquery or use QUALIFY.

Watch it explained

Calculate running total in SQL Server 2012 — kudvenkat, 6:23