Window Frames — Running Totals and Moving Averages (ROWS / RANGE BETWEEN)
Choose the exact rows a window calculation sees, distinguish row-count windows from value ranges, and build correct cumulative and moving metrics.
// Part 01
A window is a set; a frame is the slice you calculate over
In the previous lesson, OVER (PARTITION BY ... ORDER BY ...) defined a window around each row. A frame narrows that window to a subset relative to the current row. Frames are what make a cumulative sum or a moving average different from the same aggregate over the entire partition. They do not remove rows: they only change which rows feed the calculation for each output row.
Imagine a delivery app asking for each merchant’s running daily sales, or the average of the last seven observed days. You need to make two decisions explicit: the order that establishes “before” and “after,” and whether the frame counts rows or spans values in the ordering column.
SUM/AVG, OVER, PARTITION BY, and ORDER BY in a window function. Objective: select ROWS or RANGE deliberately and explain how peers and missing dates affect the result.// Part 02
The frame boundaries, in plain language
The frame is written after the window ordering: ROWS BETWEEN start AND end or RANGE BETWEEN start AND end. Common boundaries include UNBOUNDED PRECEDING (the first row/value), n PRECEDING, CURRENT ROW, n FOLLOWING, and UNBOUNDED FOLLOWING. The frame must describe a valid interval that includes the current row or otherwise follows the SQL engine’s boundary rules.
| Frame | What it counts | Useful for |
|---|---|---|
| ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | Physical rows from partition start through this row | Stable running total when ordering is unique |
| ROWS BETWEEN 6 PRECEDING AND CURRENT ROW | This row plus up to six earlier rows | Seven observed records, regardless of date gaps |
| RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | All ordering values up through the current value, including peers | Cumulative value where tied timestamps should share a result |
| RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW | Values within six calendar days of the current date | Calendar-time lookback in PostgreSQL |
ROWS results, such as ORDER BY order_date, order_id. For a RANGE offset frame, PostgreSQL requires a single compatible ordering expression; ties are peers by design.// Part 03
Running totals: make the frame and ordering explicit
First aggregate orders to one row per store per day. Then use an explicit row frame to cumulate the daily amount. This two-stage query prevents a same-day order from appearing as several indistinguishable date rows and makes the output grain easy to verify.
With one row per store_id, order_date, date ordering is unique within each store if the data grain is enforced. If you skip the daily aggregation, several orders can share a date. A row-based running total will then move one order at a time; a range-based cumulative frame gives all orders on the same date the same cumulative result, because they are peers.
// Part 04
ROWS versus RANGE: records versus ordering values
ROWS counts physical rows in the sorted partition. A seven-row average means the current record and up to six preceding records, even if those records span weeks. RANGE groups equal ordering values as peers; with a value-offset range, it selects rows whose ordering value is inside that range. These are different business questions, not interchangeable syntax.
The second calculation only sees rows that exist in daily_sales. If a store had no orders on Tuesday, no Tuesday row exists, so the frame is not automatically padded with zero. For a true daily metric, generate a calendar series per store, left join the daily totals, and replace missing totals with zero before applying the frame.
// Part 05
Default frames and dialect notes
Leaving out the frame can be a subtle bug. In PostgreSQL, when a window aggregate has ORDER BY and no explicit frame, the default is a range from the partition start through the current row’s peer group. Thus duplicate ordering values can receive the same cumulative result. For a strictly row-by-row running total, state ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and use a deterministic ordering.
Syntax support differs. PostgreSQL supports offset RANGE frames with a compatible single sort key, including date/time plus an interval. MySQL 8 supports frame clauses, but date-range offsets and expression requirements are version-sensitive; check the target server’s window-frame documentation. SQL Server’s RANGE support is more limited and does not provide PostgreSQL-style interval offsets; use ROWS where a row-count frame is intended. Snowflake supports ROWS and RANGE, with restrictions on offset types and ordering expressions. Do not copy a range-offset query across engines without testing it.
// Part 06
Common mistakes to catch in review
1. Using ROWS for a calendar duration
ROWS 6 PRECEDING counts six records, not six days. Use a date-based RANGE frame or a date spine, depending on whether missing days should be excluded or treated as zero.
2. Leaving ORDER BY non-unique for row-wise logic
If two rows tie on the sort key, their row order can vary. Add a stable key such as order_id when a sequential row frame is the business requirement.
3. Relying on the default frame without checking peers
An ordered aggregate commonly defaults to a cumulative RANGE frame, so tied values share the same result. Write the intended frame explicitly.
4. Treating absent dates as zero without a calendar
A frame cannot include rows that are not in its input. Generate the expected date grain, left join observations, then decide whether NULL should become zero.
// Part 07
Practice: choose a frame before writing the query
Use the FreshCart orders table. State the output grain and the frame semantics before coding each answer.
- Compute each store’s daily delivered revenue and a row-by-row cumulative revenue using a unique date grain.
- Calculate the average of each store’s current and previous six observed daily rows. What changes if some dates are missing?
- Show why two rows with the same ordering timestamp may share a cumulative value under a peer-aware range frame.
- Design the date spine needed to calculate seven calendar days, including no-order days. State whether zero or NULL is appropriate for the business metric.
- Translate the running-total query to the engine you use and verify its default-frame and offset-RANGE support.
🎯 Key Takeaways
- ✓A frame selects the rows used for one window result; it does not collapse output rows.
- ✓ROWS counts physical records; RANGE reasons about ordering values and peer groups.
- ✓Specify frames explicitly for predictable running totals and moving averages.
- ✓A six-preceding ROWS frame means up to seven observations, not seven calendar days.
- ✓Missing dates require an explicit calendar spine if the metric is calendar-based.
- ✓Window-frame details and offset syntax vary across PostgreSQL, MySQL, SQL Server, and Snowflake.
Next
Continue with ranking functions, which use ordered window partitions but do not require aggregate frames.
Lesson 54 → Ranking — ROW_NUMBER, RANK, DENSE_RANKHELP US IMPROVE
How was this lesson?
Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.
Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.
Discussion
0Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.
