Window frames (ROWS / RANGE / GROUPS)

Define exactly which rows around the current row an aggregate window function covers.

Syntax

{ ROWS | RANGE | GROUPS } BETWEEN frame_start AND frame_end [EXCLUDE ...]
frame_start / frame_end:
  UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW | n FOLLOWING | UNBOUNDED FOLLOWING
-- e.g. 3-row moving average:
avg(x) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

The frame is the subset of the partition an aggregate window function (sum, avg, count, first/last_value…) looks at. Ranking functions and lag/lead ignore it.

ROWS counts physical rows: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = this row and the two before.
RANGE works on values of the ORDER BY key: RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW covers the last 7 calendar days, however many rows that is (one ORDER BY column only). Rows with an equal key (peers) are always in or out together.
GROUPS counts groups of peers.
Default with ORDER BY: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (includes ties of the current row). Without ORDER BY: the whole partition.
ROWS moving averages assume one row per period; with gaps (missing days) prefer RANGE on a date or build a date spine.
At the start of a partition the frame is simply shorter (the first row's 3-row average uses 1 row).

Examples

7-day moving average (by rows) shop

WITH daily AS (
  SELECT ordered_at::date AS day, count(*) AS orders
  FROM orders
  WHERE ordered_at >= '2025-11-01' AND ordered_at < '2025-12-01'
  GROUP BY day
)
SELECT day, orders,
       round(avg(orders) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 1) AS avg_7_rows
FROM daily
ORDER BY day;

Each value averages the current day and the six rows before it.

RANGE with an interval starter

SELECT order_id, order_date, quantity,
       sum(quantity) OVER (ORDER BY order_date RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW) AS units_last_30_days
FROM orders
ORDER BY order_date, order_id;

Covers orders in the 30 calendar days up to and including the current date, regardless of row count.

ROWS vs default RANGE with ties starter

SELECT customer_id, order_date, quantity,
       sum(quantity) OVER (ORDER BY order_date) AS range_running,
       sum(quantity) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS rows_running
FROM orders
ORDER BY order_date, customer_id;

If two orders share a date, range_running jumps by both at once; rows_running adds them one by one.

Pitfalls

Assuming ROWS BETWEEN 6 PRECEDING means 7 days when some days have no rows.
Relying on the default RANGE frame when the ORDER BY key has ties.
Expecting a frame to affect row_number(), rank(), lag() or lead().

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: RANGE supports only UNBOUNDED and CURRENT ROW (no value offsets); no GROUPS.
MySQL: RANGE with value/interval offsets supported; no GROUPS.