Sliding Window Frames: ROWS & RANGE0%

Cumulative Aggregates: Running Totals & Moving Averages

Sliding Window Frames: ROWS & RANGE

Advanced14 min read•Updated: 2026-09-12

Sliding Window Frames: ROWS & RANGE

In intermediate SQL, window functions calculate aggregates across whole partitions. In advanced analytical engineering, you often need to restrict calculations to a moving subset of rows relative to the current row—such as a 7-day rolling average or a trailing 3-order moving sum.

This dynamic sliding subset is called the Window Frame, configured using the ROWS or RANGE sub-clauses inside OVER().

Frame Specification Syntax

1{ ROWS | RANGE } BETWEEN frame_start AND frame_end

Frame Boundary Keywords:

UNBOUNDED PRECEDING: Start from the very first row in the partition.
n PRECEDING: Start n rows (or values) before the current row.
CURRENT ROW: The current row being evaluated.
n FOLLOWING: End n rows (or values) after the current row.
UNBOUNDED FOLLOWING: Extend to the very last row in the partition.

ROWS vs RANGE: The Critical Difference

Dimension	`ROWS`	`RANGE`
Unit of Measurement	Physical row count	Logical value offsets
Tie Handling	Distinguishes identical values row-by-row	Treats rows with identical `ORDER BY` values as a single peer group
Performance	Faster (counts physical offsets)	Slower (evaluates value ranges)

Important Warning

If you specify ORDER BY inside OVER() without declaring a frame, MySQL uses: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW This default means that tied values are included together in the running calculation!

Practical Example: Trailing 3-Day Moving Average

1SELECT 
  sale_date,
  daily_revenue,
  -- Compute average of: 2 preceding rows + current row (3 physical rows)
  AVG(daily_revenue) OVER (
      ORDER BY sale_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS rolling_3day_avg,
  
  -- Centered window: 1 day before, current day, 1 day after
  AVG(daily_revenue) OVER (
      ORDER BY sale_date
      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS centered_3day_avg
15FROM daily_sales;

Logical Value Offsets with RANGE in MySQL 8.0

In MySQL 8.0+, RANGE supports numeric and temporal offsets:

1-- Trailing 7 calendar days moving sum (regardless of missing calendar dates!)
2SELECT 
3    order_date,
4    amount,
5    SUM(amount) OVER (
6        ORDER BY order_date
7        RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
8    ) AS trailing_7days_revenue
9FROM orders;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the fundamental difference between ROWS and RANGE in window frame specifications?

Question 2

What is the default window frame when ORDER BY is provided in an OVER() clause without an explicit frame clause?

Question 3

Which frame specification defines a 3-row sliding window including the previous row, current row, and next row?

Question 4

How can you calculate a 14-day rolling revenue window when some dates have zero sales and are missing from the table?

Question 5

What does UNBOUNDED FOLLOWING designate as a frame boundary?

Next Lesson

Cumulative Aggregates: Running Totals & Moving Averages

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
None	Cumulative Aggregates: Running Totals & Moving Averages

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Cumulative Aggregates: Running Totals & Moving Averages

Sliding Window Frames: ROWS & RANGE

Advanced14 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Sliding Window Frames: ROWS & RANGE

This dynamic sliding subset is called the Window Frame, configured using the ROWS or RANGE sub-clauses inside OVER().

Frame Specification Syntax

1{ ROWS | RANGE } BETWEEN frame_start AND frame_end

Frame Boundary Keywords:

UNBOUNDED PRECEDING: Start from the very first row in the partition.
n PRECEDING: Start n rows (or values) before the current row.
CURRENT ROW: The current row being evaluated.
n FOLLOWING: End n rows (or values) after the current row.
UNBOUNDED FOLLOWING: Extend to the very last row in the partition.

ROWS vs RANGE: The Critical Difference

Dimension	`ROWS`	`RANGE`
Unit of Measurement	Physical row count	Logical value offsets
Tie Handling	Distinguishes identical values row-by-row	Treats rows with identical `ORDER BY` values as a single peer group
Performance	Faster (counts physical offsets)	Slower (evaluates value ranges)

Important Warning

Practical Example: Trailing 3-Day Moving Average

1SELECT 
  sale_date,
  daily_revenue,
  -- Compute average of: 2 preceding rows + current row (3 physical rows)
  AVG(daily_revenue) OVER (
      ORDER BY sale_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS rolling_3day_avg,
  
  -- Centered window: 1 day before, current day, 1 day after
  AVG(daily_revenue) OVER (
      ORDER BY sale_date
      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS centered_3day_avg
15FROM daily_sales;

Logical Value Offsets with RANGE in MySQL 8.0

In MySQL 8.0+, RANGE supports numeric and temporal offsets:

1-- Trailing 7 calendar days moving sum (regardless of missing calendar dates!)
2SELECT 
3    order_date,
4    amount,
5    SUM(amount) OVER (
6        ORDER BY order_date
7        RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
8    ) AS trailing_7days_revenue
9FROM orders;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the fundamental difference between ROWS and RANGE in window frame specifications?

Question 2

What is the default window frame when ORDER BY is provided in an OVER() clause without an explicit frame clause?

Question 3

Which frame specification defines a 3-row sliding window including the previous row, current row, and next row?

Question 4

How can you calculate a 14-day rolling revenue window when some dates have zero sales and are missing from the table?

Question 5

What does UNBOUNDED FOLLOWING designate as a frame boundary?

Next Lesson

Cumulative Aggregates: Running Totals & Moving Averages

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
None	Cumulative Aggregates: Running Totals & Moving Averages

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.