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.