Window frame specification (ROWS BETWEEN) in SQL - Deep Dive

Overview - Window frame specification (ROWS BETWEEN)

What is it?

Window frame specification with ROWS BETWEEN defines which rows around the current row are included in a window function calculation. It lets you control the exact range of rows to consider, based on their position relative to the current row. This helps perform calculations like running totals or moving averages over a specific set of rows. It is part of SQL's window functions that work on a set of rows without collapsing the result into fewer rows.

Why it matters

Without window frame specification, window functions would always consider all rows in the partition, which limits flexibility. ROWS BETWEEN lets you focus calculations on a moving window of rows, enabling powerful analytics like trends, rankings, and comparisons over time or sequences. Without it, you would need complex self-joins or subqueries, which are slower and harder to write.

Where it fits

Learners should first understand basic SQL SELECT queries and aggregate functions. Then they should learn window functions and PARTITION BY and ORDER BY clauses. After mastering ROWS BETWEEN, they can explore advanced window functions and performance tuning.

Mental Model

Core Idea

ROWS BETWEEN defines a sliding window of rows around the current row to precisely control which rows a window function uses for its calculation.

Think of it like...

Imagine you are looking through a moving window on a train, and you only see a few houses before and after your current position. The window frame tells you exactly which houses you can see and count as you move along the track.

Current row: [X]
Window frame: [X-2, X-1, X, X+1, X+2]

Example:
Row positions: 1  2  3  4  5  6  7
Window frame for row 4: rows 2 to 6

┌─────────────┐
│ 2 3 [4] 5 6 │ <- rows included in frame
└─────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding window functions basics

Concept: Window functions perform calculations across a set of rows related to the current row without collapsing the result.

A window function like ROW_NUMBER() assigns a number to each row based on order but keeps all rows visible. It uses PARTITION BY to group rows and ORDER BY to sort them within each group.

Result

You get a new column with row numbers for each partition, but all original rows remain.

Understanding that window functions keep all rows and add calculations helps grasp why controlling the frame matters.

2

Foundation

What is a window frame in SQL?

Concept: A window frame defines the subset of rows around the current row that a window function uses for its calculation.

By default, some window functions consider all rows in the partition. The frame lets you limit this to a smaller range, like the current row and the two before it.

Result

You can calculate things like running totals or moving averages over just a few rows instead of the whole partition.

Knowing that the frame controls the calculation range is key to customizing window function behavior.

3

Intermediate

ROWS BETWEEN syntax and options

🤔Before reading on: do you think ROWS BETWEEN can specify rows before and after the current row independently? Commit to yes or no.

Concept: ROWS BETWEEN lets you specify a start and end point relative to the current row, using keywords like UNBOUNDED PRECEDING, CURRENT ROW, and N PRECEDING/FOLLOWING.

Syntax example: ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING means include 2 rows before and 1 row after the current row. You can also use: - UNBOUNDED PRECEDING: from the first row in the partition - UNBOUNDED FOLLOWING: to the last row in the partition - CURRENT ROW: the current row itself

Result

You get a flexible window frame that can slide and vary in size depending on the current row's position.

Understanding the syntax lets you precisely control the window size and direction for calculations.

4

Intermediate

Difference between ROWS and RANGE frames

🤔Before reading on: do you think ROWS and RANGE frame types always behave the same? Commit to yes or no.

Concept: ROWS counts physical rows relative to the current row, while RANGE considers logical values in the ORDER BY column, including ties.

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW includes exactly one row before and the current row. RANGE BETWEEN 1 PRECEDING AND CURRENT ROW includes all rows with ORDER BY values within 1 unit before the current row's value. This means RANGE can include multiple rows if they have the same ORDER BY value.

Result

ROWS gives a fixed number of rows; RANGE can include variable rows based on values.

Knowing this difference prevents bugs when your data has duplicates or you want value-based windows.

5

Intermediate

Using ROWS BETWEEN for running totals

Concept: You can calculate a running total by summing all rows from the start of the partition up to the current row using ROWS BETWEEN.

Example: SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) This sums sales from the first date up to the current date for each row.

Result

Each row shows the total sales so far, growing as you move down the rows.

Applying ROWS BETWEEN for cumulative sums is a common and powerful use case.

6

Advanced

Handling frame boundaries at partition edges

🤔Before reading on: do you think ROWS BETWEEN can reference rows that don't exist (like before the first row)? Commit to yes or no.

Concept: When the frame references rows outside the partition (like 2 preceding rows for the first row), SQL automatically adjusts the frame to available rows only.

If you say ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for the first row, it only includes the first row itself because no preceding rows exist. This prevents errors and keeps calculations consistent.

Result

Window functions gracefully handle edges without failing or returning NULL unexpectedly.

Understanding automatic frame adjustment helps avoid confusion about missing data at edges.

7

Expert

Performance implications of window frames

🤔Before reading on: do you think smaller window frames always improve query speed? Commit to yes or no.

Concept: Window frame size affects how much data the database processes for each row, impacting performance. Smaller frames can be faster but not always.

Databases optimize window functions differently. Some cache results or use indexes. Large frames or complex ORDER BY can slow queries. Choosing the right frame balances accuracy and speed. Example: Using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for a large partition can be slower than a fixed small frame.

Result

Understanding performance helps write efficient queries and avoid slow reports.

Knowing how frame size impacts execution guides better query design in production.

Under the Hood

Internally, the database engine processes window functions by scanning rows in the partition ordered as specified. The ROWS BETWEEN frame tells the engine which rows to include for each current row's calculation by counting physical row positions. The engine dynamically adjusts the frame boundaries for each row, efficiently reusing computations when possible to avoid recalculating over the entire partition every time.

Why designed this way?

The ROWS BETWEEN syntax was designed to give precise control over the window size and direction, enabling a wide range of analytics. It balances expressiveness and performance by letting users specify exact row ranges. Alternatives like RANGE were added later to handle value-based frames, but ROWS remains fundamental for position-based calculations.

Partition rows ordered by key:

┌─────────────┐
│ Row 1       │
│ Row 2       │
│ Row 3       │
│ Row 4 (cur) │
│ Row 5       │
│ Row 6       │
└─────────────┘

For each current row:
  ┌─────────────────────────────┐
  │ Calculate using rows from   │
  │ start_frame to end_frame    │
  └─────────────────────────────┘

Frame moves as current row moves.

Myth Busters - 4 Common Misconceptions

Quick: Does ROWS BETWEEN always include the current row? Commit yes or no.

Common Belief: ROWS BETWEEN always includes the current row by default.

Reality: ROWS BETWEEN requires explicit frame boundaries; if you specify a frame that excludes the current row, it won't be included.

Why it matters: Assuming the current row is always included can cause wrong calculations, like missing the current row's value in sums.

Quick: Does ROWS BETWEEN consider the values in ORDER BY or just row positions? Commit your answer.

Common Belief: ROWS BETWEEN frames rows based on ORDER BY values, including all rows with similar values.

Reality: ROWS BETWEEN counts physical row positions, ignoring ORDER BY values; it does not group ties automatically.

Why it matters: Confusing ROWS with RANGE can lead to unexpected frame sizes and incorrect results when data has duplicates.

Quick: Can ROWS BETWEEN reference rows outside the partition? Commit yes or no.

Common Belief: ROWS BETWEEN can cause errors if frame boundaries go beyond partition edges.

Reality: SQL automatically adjusts frame boundaries to available rows, preventing errors at edges.

Why it matters: Expecting errors can make learners add unnecessary checks or avoid useful frames.

Quick: Does a smaller ROWS BETWEEN frame always mean faster queries? Commit yes or no.

Common Belief: Smaller frames always improve query performance.

Reality: Performance depends on many factors; sometimes smaller frames add overhead due to repeated calculations or lack of optimization.

Why it matters: Assuming smaller frames are always better can lead to inefficient query designs.

Expert Zone

1

Some databases optimize window frames by caching partial aggregates, but this depends on frame type and function used.

2

The interaction between ROWS BETWEEN and ORDER BY can affect parallel query execution and indexing strategies.

3

Using ROWS BETWEEN with non-unique ORDER BY columns can produce subtle differences in results depending on physical row order.

When NOT to use

Avoid ROWS BETWEEN when you need value-based frames that include all rows with the same ORDER BY value; use RANGE BETWEEN instead. Also, for very large partitions with unbounded frames, consider pre-aggregating data or using summary tables to improve performance.

Production Patterns

Common patterns include running totals with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, moving averages with fixed preceding and following rows, and lag/lead calculations combined with frames for trend analysis. Production queries often combine frames with PARTITION BY to segment data by categories.

Connections

Sliding window algorithms

Both define a moving subset of data to compute results incrementally.

Understanding sliding windows in algorithms helps grasp how window frames move over rows to compute aggregates efficiently.

Time series analysis

Window frames enable calculations like moving averages and cumulative sums common in time series data.

Knowing window frames deepens understanding of how time-based trends and smoothing are computed in databases.

Signal processing

Windowing in signal processing and ROWS BETWEEN both select a subset of data points to analyze locally.

Recognizing this connection shows how local context matters in both data signals and database rows for meaningful calculations.

Common Pitfalls

#1 Using ROWS BETWEEN without specifying frame boundaries defaults to an unexpected frame.

Wrong approach:

SELECT SUM(sales) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) FROM sales_data;

Correct approach:

SELECT SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM sales_data;

Root cause:

Misunderstanding default frame behavior leads to wrong cumulative calculations.

#2 Confusing ROWS and RANGE frame types causes incorrect inclusion of rows.

Wrong approach:

SELECT SUM(sales) OVER (ORDER BY date RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) FROM sales_data;

Correct approach:

SELECT SUM(sales) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM sales_data;

Root cause:

Not knowing that RANGE includes all rows with the same ORDER BY value, while ROWS counts physical rows.

#3 Specifying frame boundaries that exclude the current row unintentionally.

Wrong approach:

SELECT AVG(score) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) FROM test_scores;

Correct approach:

SELECT AVG(score) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM test_scores;

Root cause:

Assuming current row is always included without checking frame boundaries.

Key Takeaways

ROWS BETWEEN lets you define a precise range of rows around the current row for window function calculations.

It counts physical rows relative to the current row, unlike RANGE which uses value ranges.

Frame boundaries adjust automatically at partition edges to avoid errors.

Choosing the right frame size balances calculation accuracy and query performance.

Understanding ROWS BETWEEN unlocks powerful analytics like running totals and moving averages in SQL.

Practice

1. What does the ROWS BETWEEN clause specify in a window function?

easy

A. The range of rows to include relative to the current row for calculation

B. The order in which rows are sorted before aggregation

C. The columns to group by in the query

D. The filter condition for rows in the table

Solution

1. Step 1: Understand window function frame

   The window frame defines which rows are considered for each calculation in a window function.

2. Step 2: Role of ROWS BETWEEN

   The ROWS BETWEEN clause specifies the exact range of rows relative to the current row to include in the calculation.

3. Final Answer:

   The range of rows to include relative to the current row for calculation -> Option A

4. Quick Check:

   ROWS BETWEEN controls row range = D [OK]

Hint: ROWS BETWEEN defines which rows to include around current row [OK]

Common Mistakes:

• Confusing ROWS BETWEEN with ORDER BY
• Thinking it filters rows from the table
• Mixing it up with GROUP BY

2. Which of the following is the correct syntax to specify a window frame from 2 rows before to current row using ROWS BETWEEN?

easy

A. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

B. ROWS BETWEEN CURRENT ROW AND 2 PRECEDING

C. ROWS BETWEEN 2 FOLLOWING AND CURRENT ROW

D. ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING

Solution

1. Step 1: Understand frame boundaries order

   The syntax is ROWS BETWEEN X AND Y where X is the start and Y is the end of the frame.

2. Step 2: Identify correct order for 2 rows before to current

   Since 2 rows before is the start and current row is the end, it must be ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

3. Final Answer:

   ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -> Option A

4. Quick Check:

   Start to end order matters = A [OK]

Hint: Start boundary comes first, then end boundary in ROWS BETWEEN [OK]

Common Mistakes:

• Reversing the order of boundaries
• Using FOLLOWING instead of PRECEDING incorrectly
• Omitting CURRENT ROW keyword

3. Given the table sales(date, amount) ordered by date, what is the output of this query?

SELECT date, amount, SUM(amount) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS running_sum FROM sales;

Assuming data:
date | amount
2024-01-01 | 100
2024-01-02 | 200
2024-01-03 | 300

medium

A. [{"date": "2024-01-01", "amount": 100, "running_sum": 300}, {"date": "2024-01-02", "amount": 200, "running_sum": 500}, {"date": "2024-01-03", "amount": 300, "running_sum": 600}]

B. [{"date": "2024-01-01", "amount": 100, "running_sum": 100}, {"date": "2024-01-02", "amount": 200, "running_sum": 200}, {"date": "2024-01-03", "amount": 300, "running_sum": 300}]

C. [{"date": "2024-01-01", "amount": 100, "running_sum": 100}, {"date": "2024-01-02", "amount": 200, "running_sum": 300}, {"date": "2024-01-03", "amount": 300, "running_sum": 500}]

D. [{"date": "2024-01-01", "amount": 100, "running_sum": null}, {"date": "2024-01-02", "amount": 200, "running_sum": 300}, {"date": "2024-01-03", "amount": 300, "running_sum": 500}]

Solution

1. Step 1: Understand frame range

   The frame includes the current row and 1 row before it, so for each row sum amounts of current and previous row.

2. Step 2: Calculate running sums

   Row 1: only 100 (no previous row), Row 2: 100+200=300, Row 3: 200+300=500.

3. Final Answer:

   [{"date": "2024-01-01", "amount": 100, "running_sum": 100}, {"date": "2024-01-02", "amount": 200, "running_sum": 300}, {"date": "2024-01-03", "amount": 300, "running_sum": 500}] -> Option C

4. Quick Check:

   Sum current + 1 preceding = A [OK]

Hint: Sum includes current and 1 previous row amounts [OK]

Common Mistakes:

• Including 2 rows before instead of 1
• Ignoring the order of rows
• Assuming NULL for first row sum

4. Identify the error in this SQL window function:

SELECT id, value, AVG(value) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND 2 PRECEDING) AS avg_val FROM data;

medium

A. ORDER BY cannot be used with ROWS BETWEEN

B. The frame boundaries are reversed; start must be before end

C. Missing PARTITION BY clause

D. AVG cannot be used as a window function

Solution

1. Step 1: Check frame boundaries order

   The frame must start with the earlier row and end with the later row. Here, CURRENT ROW is after 2 PRECEDING, so order is reversed.

2. Step 2: Identify correct syntax

   The correct syntax is ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, not the other way around.

3. Final Answer:

   The frame boundaries are reversed; start must be before end -> Option B

4. Quick Check:

   Frame start must be before end = C [OK]

Hint: Start boundary must be earlier than end boundary [OK]

Common Mistakes:

• Swapping start and end boundaries
• Assuming PARTITION BY is mandatory
• Thinking ORDER BY disallows ROWS BETWEEN

5. You want to calculate a moving average of the last 3 sales amounts including the current row, but your data has gaps in dates. Which window frame specification ensures exactly 3 rows are included regardless of date gaps?

hard

A. RANGE BETWEEN INTERVAL '2' DAY PRECEDING AND CURRENT ROW

B. RANGE BETWEEN 2 PRECEDING AND CURRENT ROW

C. ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING

D. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

Solution

1. Step 1: Understand difference between ROWS and RANGE

   ROWS counts physical rows, while RANGE counts logical values like dates, which can skip rows if dates have gaps.

2. Step 2: Choose frame to include 3 rows

   Using ROWS BETWEEN 2 PRECEDING AND CURRENT ROW always includes exactly 3 rows regardless of date gaps.

3. Final Answer:

   ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -> Option D

4. Quick Check:

   ROWS counts rows, RANGE counts values = B [OK]

Hint: Use ROWS to count exact rows, not RANGE for gaps [OK]

Common Mistakes:

• Using RANGE which depends on value gaps
• Confusing FOLLOWING with PRECEDING
• Assuming RANGE counts rows