ROWS BETWEEN

The ROWS BETWEEN clause defines the window frame used by a window function. It controls exactly which rows are included in a calculation.

This is especially useful for cumulative sums, rolling totals, and moving averages.

Unlike PARTITION BY, which divides rows into groups, ROWS BETWEEN controls which rows before or after the current one should be included in the computation.

You can use ROWS BETWEEN to define the window frame as follows:

Copy
SELECT column,
  window_function(...) OVER (
    ORDER BY column
    ROWS BETWEEN frame_start AND frame_end
  ) AS result
FROM table;

In general, ROWS BETWEEN defines the exact range of rows relative to the current row that a window function uses for its calculation.

Below are the common frame types:

• UNBOUNDED PRECEDING: from the first row to current
• CURRENT ROW: the row being processed
• UNBOUNDED FOLLOWING: from current to the last row
• N PRECEDING / N FOLLOWING: a specific number of rows before or after

Assume we have the following table:

daily_progress

We want to calculate the cumulative progress for each user.

Copy
SELECT user_id,
  date,
  progress_increment,
  SUM(progress_increment) OVER (
    PARTITION BY user_id
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_progress
FROM daily_progress;

The query returns the following:

Result:

Here, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes all rows from the start up to the current one, producing a cumulative total per user.