Window frame specification (ROWS BETWEEN) in SQL - Step-by-Step Execution

Concept Flow - Window frame specification (ROWS BETWEEN)

Start with full ordered data set

↓

Define window function partition

↓

Specify frame: ROWS BETWEEN X AND Y

↓

For each row, select frame rows

↓

Apply aggregation over frame rows

↓

Return result for current row

↓

Repeat for next row until end

The window frame defines which rows around the current row are included for the window function calculation.

Execution Sample

SQL

SELECT emp_id, salary,
  SUM(salary) OVER (ORDER BY emp_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_window
FROM employees
ORDER BY emp_id;

Calculates the sum of salaries for each employee including the previous and next employee by emp_id order.

Execution Table

Step	Current emp_id	Frame Rows emp_id	Frame Rows salary	SUM(salary) over frame
1	1	[1,2]	[1000, 1500]	2500
2	2	[1,2,3]	[1000, 1500, 1200]	3700
3	3	[2,3,4]	[1500, 1200, 1300]	4000
4	4	[3,4,5]	[1200, 1300, 1100]	3600
5	5	[4,5]	[1300, 1100]	2400
6	End	No more rows	N/A	N/A

💡 Reached last row, no more rows to process.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
Current emp_id	N/A	1	2	3	4	5	End
Frame Rows emp_id	N/A	[1,2]	[1,2,3]	[2,3,4]	[3,4,5]	[4,5]	N/A
SUM(salary) over frame	N/A	2500	3700	4000	3600	2400	N/A

Key Moments - 2 Insights

Why does the frame for emp_id=1 only include two rows instead of three?

Because the frame is defined as 1 row before and 1 row after the current row. For emp_id=1, there is no preceding row, so only the current and next rows are included (see execution_table row 1).

What happens to the frame when the current row is near the end of the dataset?

The frame shrinks because there are fewer rows after the current row. For emp_id=5, only the current and previous rows are included since there is no following row (see execution_table row 5).

Visual Quiz - 3 Questions

Look at the execution table, what is the sum of salaries for emp_id=3?

A 4000

B 3700

C 3600

D 2500

💡 Hint

Check the row where Current emp_id is 3 in the execution_table.

At which step does the frame include exactly three rows?

A Step 1

B Step 5

C Step 2

D Step 6

💡 Hint

Look at the Frame Rows emp_id column in execution_table for the number of rows included.

If the frame was changed to ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, what would be the frame rows for emp_id=3?

A [2,3]

B [1,2,3]

C [3,4]

D [1,2]

💡 Hint

The frame includes the current row and two preceding rows, check variable_tracker for similar patterns.

Concept Snapshot

Window frame specification (ROWS BETWEEN) defines which rows around the current row are included in the window function.
Syntax example: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
The frame moves with each row, including rows before and after as specified.
If no rows exist before or after, the frame shrinks accordingly.
Used to calculate running totals, moving averages, and other aggregates over a sliding window.

Full Transcript

This visual execution shows how the window frame specification ROWS BETWEEN works in SQL. We start with an ordered dataset by emp_id. For each row, the frame is defined as one row before and one row after the current row. The frame rows are selected accordingly, shrinking at the edges where preceding or following rows do not exist. The SUM function is applied over the salaries in the frame, producing a running sum that includes neighboring rows. The execution table traces each step, showing the current row, frame rows, and the sum result. The variable tracker shows how the current emp_id, frame rows, and sum change step-by-step. Key moments clarify why frames shrink at dataset edges. The quiz tests understanding of frame contents and sums at specific steps. This helps beginners see how window frames slide and aggregate data 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