Frames: Running Totals & Moving Averages

Why frames earn their keep

"Revenue to date," "7-day moving average," "each row's share of the total": three of the most common analytics asks, and all three are the same tool. A frame lets a window aggregate see a range of rows around the current one and collapse them into a single value per row, while keeping every original row intact. LAG peeks at one neighbor. A frame sums or averages a whole sliding stretch, with no self-join and no GROUP BY that would flatten your row-level detail away.

The frame is the third piece of OVER

PARTITION BY chooses the group, ORDER BY fixes the sequence inside it, and the frame decides which of those ordered rows the aggregate actually sees for the current row. Bounds run from UNBOUNDED PRECEDING (start of the partition) through n PRECEDING, CURRENT ROW, n FOLLOWING, to UNBOUNDED FOLLOWING (end).

SELECT
  order_date, revenue,
  SUM(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- running total
  ) AS running_total,
  AVG(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW           -- current + 2 prior = 3-row avg
  ) AS moving_avg_3,
  ROUND(revenue * 100.0 / SUM(revenue) OVER (), 1) AS pct_of_total  -- empty OVER() = whole set
FROM daily_revenue;
-- order_date  revenue  running_total  moving_avg_3  pct_of_total
-- 2024-01-01      100            100         100.0          16.7
-- 2024-01-02      200            300         150.0          33.3
-- 2024-01-03      300            600         200.0          50.0

Three shapes, one clause:

• Running total: ORDER BY plus ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW accumulates the first row through the current one.
• Moving average: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is the current row plus the 2 before it (3 rows). Early rows average over fewer rows, and that is correct.
• Per-group or grand total: with no ORDER BY and no frame, the aggregate spans the entire partition. SUM(revenue) OVER () totals the whole result set, while SUM(revenue) OVER (PARTITION BY customer_id) totals just that customer. Divide by it for percent-of-total. Add an ORDER BY and it silently turns into a running total instead.

Scrub the current row down each frame and watch the aggregate recompute. First the running total (the frame grows one row wider each step):

SUM() OVER (ROWS UNBOUNDED PRECEDING → CURRENT ROW)

Step 1 / 3

row	value	sum
in frame: Jan 1	100	100
Jan 2	200
Jan 3	300

At Jan 1, the frame covers 1 row → SUM = 100.

Then the 3-row moving average (a fixed-width window that slides, so early rows average over fewer rows):

AVG() OVER (ROWS 2 PRECEDING → CURRENT ROW)

Step 1 / 3

row	value	avg
in frame: Jan 1	100	100
Jan 2	200
Jan 3	300

At Jan 1, the frame covers 1 row → AVG = 100.

To make any of these per-customer, add PARTITION BY customer_id inside OVER.

RANGE vs ROWS: the default frame is a trap

Every running total above spelled out ROWS BETWEEN .... That was deliberate. When you put an ORDER BY on an aggregate window but omit the frame, SQL does not leave the window open, it fills in RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is not the same as ROWS. ROWS counts physical rows; RANGE pulls in every row tied with the current one on the ORDER BY key. On a date that repeats, that difference is a wrong number hiding in plain sight.

Run the demo below. It seeds a daily_sales table where two rows share 2024-01-02 and totals revenue both ways:

• default_range is SUM(revenue) OVER (ORDER BY sale_date) with no frame, so it inherits the RANGE default.
• explicit_rows spells out ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and adds revenue DESC as a tiebreaker so accumulation across the tied rows is deterministic.

Watch the two tied rows: under RANGE they both jump to the same total (every peer is summed at once), while ROWS climbs one physical row at a time. For a deterministic row-by-row running total, always write ROWS BETWEEN.

The same frame default bites the positional functions

FIRST_VALUE, LAST_VALUE, and NTH_VALUE read a specific row inside the frame, so the current-row frame default that a running total relies on quietly makes LAST_VALUE report the current row instead of the partition's last, until you widen the frame to UNBOUNDED FOLLOWING. Level 5 teaches that positional-function trap in depth and grades it, so the full deep dive lives there; the running-total frame above is all L4's graded exercises need.

Pitfalls

• Integer division zeroes your percentages. revenue / SUM(...) on integer columns floors to 0. Multiply by 100.0 (a float) first, as above. AVG already returns a float, so moving averages are safe.
• OVER () is not OVER (PARTITION BY customer_id). The empty version totals everyone. The practice wants each customer's own total, so partition it.
• You cannot filter a window result in WHERE. Windows are computed in SELECT, after WHERE runs. Snowflake and BigQuery offer a QUALIFY clause for this shorthand. Postgres and SQLite have none, so wrap the window in a CTE and filter outside it.

Execution mode: these exercises have you write a multi-statement script. Lead with DELETE FROM <target>; so a re-run stays idempotent, then a single INSERT ... SELECT carries the window expressions, and hidden assertions check both the frame math and the row count.