Window frames, precisely

A window function looks at a set of rows related to the current row. Three parts of the OVER clause decide which rows: PARTITION BY splits the data, ORDER BY sorts each partition, and the frame picks a range of rows around the current one.

The default frame surprises people

When you write ORDER BY in a window without an explicit frame, the default is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

RANGE treats rows with the same ORDER BY value as peers. So a running total ordered by date includes all rows of the current date, not just the rows up to the current one. If several orders share a date, they all show the same running total.

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_default,
       SUM(amount) OVER (ORDER BY order_date, order_id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows
FROM orders;

The second version increases on every row, because ROWS counts physical rows and the tiebreaker makes the order deterministic.

Three frame modes

• ROWS: a number of physical rows before and after. Good for "the previous 6 rows".
• RANGE: a value distance on the ORDER BY column. In PostgreSQL you can write RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW for a true seven day window that copes with missing days.
• GROUPS: a number of peer groups. Useful when you want "the previous 3 distinct dates" regardless of how many rows each date has.

Rolling seven days, correctly

SELECT day, orders,
       AVG(orders) OVER (ORDER BY day
            RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) AS avg_7d
FROM daily_orders;

With ROWS BETWEEN 6 PRECEDING, a missing day would silently stretch the window to eight calendar days. RANGE with an interval respects the calendar.

Functions that ignore frames

Ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE) and LAG/LEAD ignore the frame entirely. FIRST_VALUE, LAST_VALUE and NTH_VALUE respect it, which is why LAST_VALUE with the default frame often returns the current row instead of the last one in the partition. Fix it with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.