ROWS BETWEEN

The ROWS BETWEEN clause defines the window frame for a window function. It specifies which rows are included in the calculation.

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

Unlike PARTITION BY, which divides rows into groups, ROWS BETWEEN determines which rows before and/or after the current one are included in the calculation.

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 up to the current row
• CURRENT ROW: the row being processed
• UNBOUNDED FOLLOWING: from the current row to the last row
• N PRECEDING / N FOLLOWING: a specific number of rows before or after the current row

Assume we have the following table:

daily_progress

We want to calculate each user’s cumulative progress over time.

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 first to the current, producing a running total for each user.

• For cumulative totals (e.g., running sum of sales or progress)
• For moving averages (e.g., last 7 days)
• For sliding comparisons across rows

It gives you fine-grained control over how much of the dataset each calculation should consider.