The frame is the part of a window function that says which rows the function may see. Every window has one, even when you do not write it, and choosing it deliberately is the difference between a running total, a moving average, and a cumulative sum that jumps around. The syntax is short. There is a start point, there is an end point, and there is a direction. The behaviour it controls is what makes these functions useful.
The previous lesson in this module covered "How Do RANK, DENSE_RANK and ROW_NUMBER Differ?", and the next lesson in this module is about "How Do LAG and LEAD Compare Rows to Previous or Next?".
How do frames compute running totals and moving averages?
A running total is the simplest of the window functions there is, and it needs two things. It needs an ORDER BY so that the rows have a sequence, and it needs a frame that reaches back to the beginning of the partition.
SELECT r.rep_name, s.sale_date,
ROUND(s.quantity * s.unit_price * (1 - s.discount), 2) AS revenue,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount))
OVER (PARTITION BY r.rep_id ORDER BY s.sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS running_total
FROM sales s JOIN reps r ON r.rep_id = s.rep_id
WHERE r.rep_id = 1
ORDER BY s.sale_date;rep_name sale_date revenue running_total
------------- ---------- ------- -------------
Ada Whitfield 2025-03-03 320.0 320.0
Ada Whitfield 2025-03-05 1008.9 1328.9
Ada Whitfield 2025-03-24 285.0 1613.9
Ada Whitfield 2025-04-14 240.0 1853.9
Ada Whitfield 2025-05-02 585.0 2438.9
Ada Whitfield 2025-05-19 480.0 2918.9The frame says this. It goes from the very start of the partition up to and including the current row. The UNBOUNDED PRECEDING means "as far back as the partition goes", and the CURRENT ROW means "up to here". Those two boundaries are what you change when you want a different window.
The running total climbs, and it finishes at Ada's quarter total of 2918.90. Every row shows what Ada had sold up to that date, and the last row shows the whole quarter.
What is the default frame, and why does it matter?
You leave the frame out entirely, and the same numbers appear.
SELECT r.rep_name, s.sale_date,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount))
OVER (PARTITION BY r.rep_id ORDER BY s.sale_date), 2) AS default_frame
FROM sales s JOIN reps r ON r.rep_id = s.rep_id
WHERE r.rep_id = 1
ORDER BY s.sale_date;rep_name sale_date default_frame
------------- ---------- -------------
Ada Whitfield 2025-03-03 320.0
Ada Whitfield 2025-03-05 1328.9
Ada Whitfield 2025-03-24 1613.9
Ada Whitfield 2025-04-14 1853.9
Ada Whitfield 2025-05-02 2438.9
Ada Whitfield 2025-05-19 2918.9They are identical, because the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and the RANGE means "every row that ties with the current one". You only notice the choice when the ordering column has repeated values.
Ada's sale dates are all distinct, so no rows tie, and the RANGE behaves exactly like the ROWS.
The two frames part company as soon as the ordering column has duplicates.
SELECT s.sale_id, s.discount,
ROUND(s.quantity * s.unit_price * (1 - s.discount), 2) AS revenue,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)) OVER (ORDER BY s.discount), 2) AS range_frame,
ROUND(SUM(s.quantity * s.unit_price * (1 - s.discount)) OVER (ORDER BY s.discount
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS rows_frame
FROM sales s
ORDER BY s.discount, s.sale_id
LIMIT 8;sale_id discount revenue range_frame rows_frame
------- -------- ------- ----------- ----------
1 0.0 320.0 13315.5 320.0
3 0.0 840.0 13315.5 1160.0
5 0.0 780.0 13315.5 1940.0
6 0.0 750.0 13315.5 2690.0
7 0.0 512.0 13315.5 3202.0
9 0.0 285.0 13315.5 3487.0
10 0.0 600.0 13315.5 4087.0
11 0.0 384.0 13315.5 4471.0Every sale with no discount ties, so the RANGE frame includes all of them in every row's total. It is 13315.50 for every one of the first rows, because at that point in the ordering every undiscounted sale is "at or before" the current row. Notice that the frame here is working on the discount column, and not on the date.
The ROWS frame counts the physical rows instead, and it accumulates 320, then 1160, then 1940.
If your running total must advance one sale at a time, you need the ROWS. If it must include the tied rows together, you need the RANGE. This is the single most common source of a "wrong" running total, because both frames look right until the data has a repeated value in the ordering column.
How do you build a moving average?
A moving average is a frame with a fixed number of rows before the current one. The frame clause does all of the work, and the average function only has to be told how far to look.
SELECT r.rep_name, s.sale_date,
ROUND(s.quantity * s.unit_price * (1 - s.discount), 2) AS revenue,
ROUND(AVG(s.quantity * s.unit_price * (1 - s.discount))
OVER (PARTITION BY r.rep_id ORDER BY s.sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM sales s JOIN reps r ON r.rep_id = s.rep_id
WHERE r.rep_id = 1
ORDER BY s.sale_date;rep_name sale_date revenue moving_avg_3
------------- ---------- ------- -------------
Ada Whitfield 2025-03-03 320.0 320.0
Ada Whitfield 2025-03-05 1008.9 664.45
Ada Whitfield 2025-03-24 285.0 537.97
Ada Whitfield 2025-04-14 240.0 511.3
Ada Whitfield 2025-05-02 585.0 370.0
Ada Whitfield 2025-05-19 480.0 435.0There are two rows before the current one, and the current one. That is a three-sale average.
The first row has no two predecessors, so the frame simply contains one row, and the average is 320. The rolling window then slides, one sale at a time, and each row gets the mean of its own little group.
The same frame in the other direction produces a centred average, which smooths a series by looking one sale ahead as well as one behind. That kind of average reacts slowly to a sudden change, which is exactly why it is easier to read on a chart.
SELECT r.rep_name, s.sale_date,
ROUND(AVG(s.quantity * s.unit_price * (1 - s.discount))
OVER (PARTITION BY r.rep_id ORDER BY s.sale_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 2) AS centered_avg
FROM sales s JOIN reps r ON r.rep_id = s.rep_id
WHERE r.rep_id = 1
ORDER BY s.sale_date;rep_name sale_date centered_avg
------------- ---------- -------------
Ada Whitfield 2025-03-03 664.45
Ada Whitfield 2025-03-05 537.97
Ada Whitfield 2025-03-24 511.3
Ada Whitfield 2025-04-14 370.0
Ada Whitfield 2025-05-02 435.0
Ada Whitfield 2025-05-19 532.5The average of 664.45 on the first row is the mean of the first two sales. There is no sale before it, so the frame simply reaches forward.
The frame boundaries always shrink at the edges of the partition instead of erroring, and that is the behaviour to expect. A short frame gives you a smaller average, and not a failure.
How do you compute a share of the whole?
You use two windows at once, the one partitioned and the one not, divided against each other. The partitioned window gives the region total, the empty window gives the company total, and the division turns both of them into a share.
WITH sale_rows AS (
SELECT rg.region_name, r.rep_name, s.sale_date,
s.quantity * s.unit_price * (1 - s.discount) AS revenue
FROM sales s
JOIN reps r ON r.rep_id = s.rep_id
JOIN regions rg ON rg.region_id = r.region_id
),
windowed AS (
SELECT region_name, rep_name, sale_date,
SUM(revenue) OVER (PARTITION BY region_name) AS region_total,
SUM(revenue) OVER () AS company_total
FROM sale_rows
)
SELECT region_name, rep_name, sale_date,
ROUND(region_total, 2) AS region_total,
ROUND(100.0 * region_total / company_total, 2) AS pct_of_company
FROM windowed
WHERE region_name = 'North'
ORDER BY sale_date
LIMIT 6;region_name rep_name sale_date region_total pct_of_company
----------- ------------- ---------- ----------- --------------
North Ada Whitfield 2025-03-03 6192.4 32.81
North Ada Whitfield 2025-03-05 6192.4 32.81
North Bruno Kessler 2025-03-07 6192.4 32.81
North Ada Whitfield 2025-03-24 6192.4 32.81
North Bruno Kessler 2025-03-26 6192.4 32.81
North Ada Whitfield 2025-04-14 6192.4 32.81The North region is 32.81 percent of the company.
There are two details that make this work. The windows are computed in a separate query level from the filter, because a WHERE in the same level would remove the rows before the window saw them, and the company total would collapse to the North alone. The filter runs at the end, on rows that already carry their window values.
And the numerator is the region's whole total and not the individual sale, so the percentage repeats down the block instead of changing on every row. If the numerator were a single sale, the column would give you that sale's share and not the region's share.
Quick reference
- The frame says which rows the function may see. There is a start point, there is an end point, and there is a direction.
ROWScounts the physical rows, andRANGEincludes every row that ties with the current one.UNBOUNDED PRECEDINGtoCURRENT ROWis the running-total frame, and it is the default when theORDER BYis present.n PRECEDINGtoCURRENT ROWgives a trailing average of the n rows, and you addn FOLLOWINGto centre it.- You compute the windows in a separate query level from the filter, or the filter will shrink the window.
Try it yourself: frame drill. For the sales of Ada Whitfield, write one query that produces a trailing two-sale average, and another that produces a quarter-to-date total. Then explain in one sentence why the first one needs an explicit frame and the second one does not.