The window is ordered by day, and the frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means "from the first row of the partition through this row". Each row's SUM covers a frame one row larger than the last. Add PARTITION BY account and the total restarts for every account — a running balance per account in one pass, where the pre-window answer was a correlated subquery summing every earlier row (quadratic work).

Frame syntax

{ROWS | RANGE} BETWEEN start AND end

start / end:
  UNBOUNDED PRECEDING     -- the partition's first row
  n PRECEDING             -- n rows (or n in value) before
  CURRENT ROW
  n FOLLOWING             -- n rows (or n in value) after
  UNBOUNDED FOLLOWING     -- the partition's last row

The short form ROWS UNBOUNDED PRECEDING means ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Some frames worth knowing by heart:

The frame is clipped at the partition's edges: on the first row, "2 preceding" has no rows to include, so the frame is just the current row. A moving average's first values are therefore averages of fewer rows — be aware of it, and blank them out with CASE WHEN ROW_NUMBER() OVER (…) >= 3 THEN … END if the report wants only full windows.

Moving averages

SELECT day, visits,
       ROUND(AVG(visits) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM daily_visits
ORDER BY day;

On 03-01 the frame is one row (30); on 03-02 two rows (30, 60 → 45); from 03-03 on, always three. ROUND matters: averages are rarely whole, and engines print different numbers of decimals. A moving SUM, MIN or MAX works the same way — "the highest price in the last five trades" is MAX(price) OVER (ORDER BY traded_at, id ROWS BETWEEN 4 PRECEDING AND CURRENT ROW).

ROWS versus RANGE

ROWS counts physical rows in the window's order. RANGE works on the value of the ORDER BY expression:

• RANGE … CURRENT ROW includes every peer — every row with the same ORDER BY value as the current row.
• RANGE BETWEEN 5 PRECEDING AND CURRENT ROW with ORDER BY price includes rows whose price is within 5 of the current row's price, however many rows that is.
• On a date column, MySQL accepts intervals: RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW is a true 7-*day* window even when some days have no rows (where ROWS BETWEEN 6 PRECEDING would reach back seven rows, possibly two weeks).

RANGE with a numeric or interval offset needs exactly one ORDER BY expression of a numeric or date type. PostgreSQL 11+ supports offsets too; SQL Server's RANGE accepts only UNBOUNDED and CURRENT ROW. There is also GROUPS (PostgreSQL, SQLite; not MySQL), which counts groups of peers.

The default frame and the ties trap

If you write an ORDER BY in the window and no frame, the frame is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

That is a running total — including all peers of the current row. With a unique ORDER BY it is the same as the ROWS version. With ties, it is not:

Rows 2 and 3 share a day, so under the default RANGE frame each one's frame includes the other: both show 190. Sometimes that is exactly right — "total as of the end of each day". When you want a balance after each transaction, add a unique tie-breaker and use ROWS. Write the frame explicitly whenever you care; readers should not have to remember the default.

Without an ORDER BY in the window, the default frame is the entire partition, which is why SUM(x) OVER (PARTITION BY g) is the group total.

The same default explains the LAST_VALUE surprise from the previous lesson: with an ORDER BY, the frame ends at the current row's last peer, so the "last value" in the frame is (a peer of) the current row. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for the partition's real last value.

Which functions use the frame

• Use it: aggregates as window functions (SUM, AVG, COUNT, MIN, MAX, …), FIRST_VALUE, LAST_VALUE, NTH_VALUE.
• Ignore it: ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST, LAG, LEAD — they always work on the whole ordered partition. MySQL accepts a frame clause on them and silently ignores it.

Running totals on top of aggregates

Frames apply after GROUP BY like every window, so a cumulative monthly total is a running SUM over a grouped query:

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       SUM(amount) AS revenue,
       SUM(SUM(amount)) OVER (ORDER BY DATE_FORMAT(order_date, '%Y-%m')
                              ROWS UNBOUNDED PRECEDING) AS cumulative
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

And a running count of flags numbers groups: SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY ts, id ROWS UNBOUNDED PRECEDING) gives each event its session number — the core of the gaps-and-islands patterns later in the course.

Percent of running total

Combine a running sum with a whole-partition sum to show progress towards a total:

SELECT day, amount,
       ROUND(100 * SUM(amount) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING)
                 / SUM(amount) OVER (), 1) AS pct_done
FROM daily_sales
ORDER BY day;

Two windows, two different frames, one row each — exactly the kind of report that once took three subqueries.

Pitfalls

• Relying on the default frame and getting equal running totals on tied rows.
• ROWS n PRECEDING used as "n days" when days can be missing.
• Forgetting that early rows of a moving average cover fewer rows.
• Unrounded moving averages.
• Expecting a frame clause to change ROW_NUMBER, RANK or LAG.

Key takeaways

• A frame chooses which rows of the partition an aggregate sees, relative to the current row.
• Running total: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW with a unique ORDER BY.
• Moving window: ROWS BETWEEN n PRECEDING AND CURRENT ROW; round averages.
• ROWS counts rows; RANGE uses values and includes peers; RANGE INTERVAL n DAY is a true day window.
• With ORDER BY the default frame is RANGE up to the current row (ties included); without it, the whole partition.