Skip to main content
Window Functions
4 / 4
0%

Window frames — ROWS BETWEEN

0/3 solved3 to unlock next
Prev★ Final lesson

Lesson 04

Window frames — ROWS BETWEEN

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.

Choosing exactly which rows to look at

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:

  • "What is the three-month trailing average of store revenue?"
  • "What share of their department's payroll does each employee take?"
  • "How many hospital appointments happened in the seven days up to each appointment?"

All three use the same aggregate functions you already know (SUM, AVG, COUNT). The only thing that changes is the frame.

The syntax

aggregate(expression) OVER (
  PARTITION BY ...
  ORDER BY     ...
  { ROWS | RANGE } BETWEEN frame_start AND frame_end
)

The boundaries you can use:

BoundaryMeaning
UNBOUNDED PRECEDINGThe first row of the partition
n PRECEDINGn rows (ROWS) or n units of value (RANGE) before the current row
CURRENT ROWThe current row (with RANGE: the current row and all its ties)
n FOLLOWINGn rows or n units of value after the current row
UNBOUNDED FOLLOWINGThe 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.

The default frame

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 BYThe whole partition
ORDER BY presentRANGE 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.

Example 1: an explicit running total

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.

Example 2: ROWS vs RANGE when there are ties

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_namesalaryrows_totalrange_total
Diana4200.004200.004200.00
David4800.009000.0013800.00
Valli4800.0013800.0013800.00
Bruce6000.0019800.0019800.00
Alexander9000.0028800.0028800.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.

Example 3: a trailing moving average

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;
monthrevenuetrailing_3m_avg
2025-01-01918.72918.72
2025-02-01758.39838.56
2025-03-011523.861066.99
2025-04-011616.741299.66
2025-05-011296.511479.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.

Example 4: the whole partition on every row

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.

Example 5: a time-based window with RANGE

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.

Under the hood

For each row, PostgreSQL works through three steps:

  1. Find the row's partition (PARTITION BY).
  2. Sort the partition (ORDER BY).
  3. Cut out the frame around the current row, then apply the aggregate to just those rows.

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.

Common mistakes

LAST_VALUE that returns the current row

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.

Writing the boundaries backwards

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.

RANGE offsets with more than one ORDER BY column

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.

Other databases

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.

Practice

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.

Key takeaways

  • The frame decides which rows inside a partition feed into an aggregate for the current row.
  • With 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.
  • Frames shrink at partition edges, so moving averages on the first rows use fewer values.
  • LAST_VALUE needs a frame that extends to UNBOUNDED FOLLOWING, or it simply returns the current row.

Window frames — ROWS BETWEEN: in depth

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.

Worked examples

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.

Common mistakes

  • Relying on the default frame. With ORDER BY, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes tied rows. Spell out ROWS when you mean rows.
  • Using ROWS for time windows when dates have gaps: "6 PRECEDING" means six rows, not six days. Use RANGE with an INTERVAL for calendar windows.
  • Calling LAST_VALUE with the default frame and getting the current row back. Extend the frame to UNBOUNDED FOLLOWING.

Other databases

  • PostgreSQL 11+ supports RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW; SQL Server only allows RANGE with UNBOUNDED or CURRENT ROW.
  • MySQL 8.0+ and SQLite 3.28+ support ROWS, RANGE and GROUPS frames.
Check yourself: What frame gives a running total that ignores ties and adds one row at a time?

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, with a unique ORDER BY.

Concept check

Quick check: Window frames — ROWS BETWEEN

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Running salary total by hire date

Enterprise Workforce AnalyticseasyPostgreSQL

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.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

3-employee moving average salary

Enterprise Workforce AnalyticseasyPostgreSQL

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.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Salary as % of department total

Enterprise Workforce AnalyticseasyPostgreSQL

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.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Final checkpoint

End-of-course high-level quiz

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?

Window Functions
4 / 4
0%

Window frames — ROWS BETWEEN

0/3 solved3 to unlock next
Prev★ Final lesson