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.

Syntax

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

ROWS BETWEEN syntax

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

Common frame types

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

Example: Running Total

Assume we have the following table:

daily_progress

user_id	date	progress_increment
1	2024-06-01	10
1	2024-06-02	20
1	2024-06-03	15
1	2024-06-04	25

We want to calculate the cumulative progress for each user.

Using ROWS BETWEEN for cumulative progress

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:

user_id	date	progress_increment	cumulative_progress
1	2024-06-01	10	10
1	2024-06-02	20	30
1	2024-06-03	15	45
1	2024-06-04	25	70

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.

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

Learn moreSign In