Lesson Description
Control exactly which rows participate in each window calculation using explicit frame clauses. You will master ROWS BETWEEN patterns for running, cumulative, and moving metrics.
Lesson 04
Lesson Description
Control exactly which rows participate in each window calculation using explicit frame clauses. You will master ROWS BETWEEN patterns for running, cumulative, and moving metrics.
Easy Project
Mini project: Build a compensation ranking dashboard query with department-level rank and delta from the top earner.
In the previous lessons, PARTITION BY decided which group a row belongs to, and ORDER BY decided the sequence inside that group. When you used SUM(...) OVER (ORDER BY ...) for a running total, PostgreSQL quietly made a third decision for you: which rows within the partition count toward the current row's value. That third piece is the frame.
Controlling the frame unlocks a set of questions that running totals alone cannot answer:
All three use the same aggregate functions you already know (SUM, AVG, COUNT). The only thing that changes is the frame.
aggregate(expression) OVER (
PARTITION BY ...
ORDER BY ...
{ ROWS | RANGE } BETWEEN frame_start AND frame_end
)
The boundaries you can use:
| Boundary | Meaning |
|---|---|
UNBOUNDED PRECEDING | The first row of the partition |
n PRECEDING | n rows (ROWS) or n units of value (RANGE) before the current row |
CURRENT ROW | The current row (with RANGE: the current row and all its ties) |
n FOLLOWING | n rows or n units of value after the current row |
UNBOUNDED FOLLOWING | The last row of the partition |
And the two most common frame modes:
ROWS counts physical rows. "1 PRECEDING" means exactly one row back.RANGE works on values of the ORDER BY column. Rows with the same value (peers) are always included together, and with a date column, "INTERVAL '6 days' PRECEDING" means "any row whose date is within six days before mine".The frame is evaluated freshly for every row, so it slides as you move down the result.
You have already been relying on defaults. Knowing them explains a lot of behavior from earlier lessons:
| What is inside OVER() | Default frame |
|---|---|
No ORDER BY | The whole partition |
ORDER BY present | RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
That second line is why a running total with tied ORDER BY values jumps several rows at once: the default is RANGE, and RANGE includes peers.
Write the running payroll from the HR sandbox with the frame spelled out:
SELECT first_name, hire_date, salary,
SUM(salary) OVER (
ORDER BY hire_date, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM employees
ORDER BY hire_date, id;
All 40 employees come back. The first row, Lex De Haan, shows 17,000.00. The last row, Eleni Zlotkey (hired 2008-01-29), shows 333,000.00, which is exactly the company's total payroll. Because hire_date, id is unique, ROWS and the default RANGE give the same answer here, but writing ROWS explicitly makes your intent clear and protects you if duplicates ever appear.
The difference only shows when the ORDER BY value repeats. Order the IT department by salary, which has a tie at 4,800:
SELECT first_name, last_name, salary,
SUM(salary) OVER (ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total,
SUM(salary) OVER (ORDER BY salary
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_total
FROM employees
WHERE department_id = 60
ORDER BY salary, id;
| first_name | salary | rows_total | range_total |
|---|---|---|---|
| Diana | 4200.00 | 4200.00 | 4200.00 |
| David | 4800.00 | 9000.00 | 13800.00 |
| Valli | 4800.00 | 13800.00 | 13800.00 |
| Bruce | 6000.00 | 19800.00 | 19800.00 |
| Alexander | 9000.00 | 28800.00 | 28800.00 |
With ROWS, David's total includes only Diana and himself. With RANGE, "current row" means "every row with salary 4,800", so Valli's salary is already included in David's total. Neither is wrong, but if you want a strictly increasing running total, use ROWS with a unique ordering.
Moving averages smooth out noisy numbers. Here is a three-month trailing average of store revenue (excluding cancelled orders), reusing the monthly CTE pattern from the LAG lesson:
WITH monthly AS (
SELECT date_trunc('month', o.order_date)::date AS month,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY 1
)
SELECT month, revenue,
ROUND(AVG(revenue) OVER (
ORDER BY month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) AS trailing_3m_avg
FROM monthly
ORDER BY month;
| month | revenue | trailing_3m_avg |
|---|---|---|
| 2025-01-01 | 918.72 | 918.72 |
| 2025-02-01 | 758.39 | 838.56 |
| 2025-03-01 | 1523.86 | 1066.99 |
| 2025-04-01 | 1616.74 | 1299.66 |
| 2025-05-01 | 1296.51 | 1479.04 |
Five rows. Look at the first two: January has no earlier months, so its "three-month" average is really a one-month average, and February averages two months. The frame shrinks at the edge of the partition instead of failing. If a partial window would mislead your audience, add COUNT(*) OVER (same frame) and hide rows where it is below 3.
A centered average works the same way with ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, which looks one row back and one row ahead.
Sometimes you want the group total beside each detail row, for example to compute a share. Use a frame that covers the whole partition:
SELECT first_name, last_name, department_id, salary,
SUM(salary) OVER (
PARTITION BY department_id
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS dept_total
FROM employees
WHERE department_id IN (20, 90)
ORDER BY department_id, salary DESC;
Five rows come back. Marketing's two employees both show a dept_total of 19,000.00, so Michael Hartstein's 13,000 is about 68.42% of it. In Executive the total is 58,000.00, and Steven King's 24,000 is 41.38%. Since this window has no ORDER BY, the default frame would already be the whole partition, so the explicit frame here is about readability. It matters a lot once you add an ORDER BY, as the common mistakes section shows.
ROWS counts rows, which is not the same as counting time. In the hospital sandbox, compare "the last 7 rows" with "the last 7 days":
SELECT appointment_date, id,
COUNT(*) OVER (
ORDER BY appointment_date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
) AS last_7_days,
COUNT(*) OVER (
ORDER BY appointment_date, id
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS last_7_rows
FROM appointments
ORDER BY appointment_date, id;
All 66 appointments come back. From the seventh row onward, last_7_rows is always 7, because it just counts rows. last_7_days tells the real story: it ranges from 1 to 7, and on the final appointment (2025-05-20) it drops to 3, because only three appointments fall between May 14 and May 20. That is the number a clinic manager actually wants. INTERVAL '6 days' PRECEDING plus the current day gives a seven-day window.
For each row, PostgreSQL works through three steps:
PARTITION BY).ORDER BY).Ranking functions such as ROW_NUMBER(), RANK() and NTILE(), as well as LAG() and LEAD(), ignore the frame entirely. Frames only affect aggregates used as window functions and the value functions FIRST_VALUE(), LAST_VALUE() and NTH_VALUE().
PostgreSQL also supports a third mode, GROUPS, which counts peer groups instead of rows, and an EXCLUDE option (for example EXCLUDE CURRENT ROW). You will rarely need them, but they exist.
SELECT first_name, department_id, salary,
LAST_VALUE(first_name) OVER (
PARTITION BY department_id ORDER BY salary DESC, id
) AS lowest_paid
FROM employees
WHERE department_id IN (20, 90);
You expect the lowest-paid person in each department. Instead every row shows its own name: Michael gets "Michael", Steven gets "Steven". The default frame ends at the current row, so the "last" value in the frame is always the current row. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING and you get "Pat" for Marketing and "Lex" for Executive, as intended.
SUM(salary) OVER (ORDER BY hire_date, id ROWS BETWEEN CURRENT ROW AND 1 PRECEDING)
ERROR: frame starting from current row cannot have preceding rows
The start boundary must come first. Write ROWS BETWEEN 1 PRECEDING AND CURRENT ROW.
Adding a tiebreaker to the time-based window in Example 5, ORDER BY appointment_date, id RANGE BETWEEN INTERVAL '6 days' PRECEDING ..., fails with:
ERROR: RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column
A value-based offset needs one column to measure distance on. Keep a single ORDER BY column for RANGE offsets, and save tiebreakers for ROWS frames.
ROWS BETWEEN works in SQL Server 2012+, Oracle, MySQL 8.0+ and SQLite 3.25+. RANGE with numeric or interval offsets is more uneven: Oracle, MySQL 8 and SQLite support it, but SQL Server only allows RANGE with UNBOUNDED and CURRENT ROW. The GROUPS mode is available in PostgreSQL 11+ and SQLite, but not in MySQL or SQL Server.
The three exercises below use the HR sandbox. The first asks for a running salary total with an explicit ROWS frame. The second asks for a centered moving average that looks one row in each direction. The third asks for each salary as a percentage of the department total using a whole-partition frame. For each, compare the first and last rows of your result with what you would expect at the edges.
ORDER BY and no explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes tied rows.ROWS counts physical rows; RANGE measures distance in values, which makes date windows like "last 7 days" possible.LAST_VALUE needs a frame that extends to UNBOUNDED FOLLOWING, or it simply returns the current row.A window frame decides which neighbouring rows a window function sees: the last seven days, the previous three orders, or everything so far. Frames turn plain aggregates into moving averages and rolling totals, which smooth out noisy daily data.
Three-order moving average of order value
WITH order_totals AS (
SELECT o.id, o.order_date, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.order_date
)
SELECT id, order_date, total,
ROUND(AVG(total) OVER (
ORDER BY order_date, id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) AS moving_avg_3
FROM order_totals
ORDER BY order_date, id;Each row averages itself and the two orders before it. The first rows average fewer values because there is less history.
Share of the department total
SELECT department_id, first_name, salary,
ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department_id), 1) AS pct_of_dept
FROM employees
ORDER BY department_id, salary DESC;With no ORDER BY in OVER, the frame is the whole partition, so SUM gives the department total on every row.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, with a unique ORDER BY.
Concept check
3 quick questions. One at a time. Instant score at the end.
Return first_name, last_name, hire_date, salary, and running_total — a cumulative sum of salary ordered by hire_date, then id. Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
No results yet
Run a query to see results here
Return first_name, last_name, hire_date, salary, and moving_avg — the average salary across the current employee and 1 row before and 1 row after (ordered by hire_date, id). Round to 2 decimal places.
No results yet
Run a query to see results here
Return first_name, last_name, department_id, salary, and pct_of_dept — each employee's salary as a percentage of their department's total salary. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Round to 2 decimal places. Order by department_id, salary descending.
No results yet
Run a query to see results here
Final checkpoint
Pass mark: 80%. Review each explanation after you submit.
1. Which clause should you use to filter grouped results?
2. What is the safest way to avoid accidental row multiplication in joins?
3. Which feature is best for ranking rows within partitions?
4. Why should final analytical queries usually include ORDER BY?
5. When query logic gets complex, what improves readability most?