Window frame (ROWS BETWEEN, RANGE BETWEEN) in PostgreSQL - Step-by-Step Execution

Concept Flow - Window frame (ROWS BETWEEN, RANGE BETWEEN)

Start Window Function

↓

Partition Data (optional)

↓

Order Data

↓

Define Frame Start and End

↓

Calculate Aggregate over Frame

↓

Return Result for Current Row

↓

Move to Next Row

↓

Repeat Until All Rows Processed

Window functions process rows in partitions and order, then apply an aggregate over a frame defined by ROWS or RANGE boundaries for each row.

Execution Sample

PostgreSQL

SELECT
  id,
  value,
  SUM(value) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_2_rows
FROM data;

Calculates a running sum of 'value' for the current row and the one before it, ordered by 'id'.

Execution Table

Step	Current Row (id)	Frame Rows Included (id)	Values in Frame	SUM(value) Result
1	1	[1]	[10]	10
2	2	[1,2]	[10,20]	30
3	3	[2,3]	[20,30]	50
4	4	[3,4]	[30,40]	70
5	5	[4,5]	[40,50]	90
6	6	[5,6]	[50,60]	110
7	7	[6,7]	[60,70]	130
8	8	[7,8]	[70,80]	150
9	9	[8,9]	[80,90]	170
10	10	[9,10]	[90,100]	190
11	11	[10,11]	[100,110]	210
12	12	[11,12]	[110,120]	230
13	13	[12,13]	[120,130]	250
14	14	[13,14]	[130,140]	270
15	15	[14,15]	[140,150]	290
16	16	[15,16]	[150,160]	310
17	17	[16,17]	[160,170]	330
18	18	[17,18]	[170,180]	350
19	19	[18,19]	[180,190]	370
20	20	[19,20]	[190,200]	390
21	21	[20,21]	[200,210]	410
22	22	[21,22]	[210,220]	430
23	23	[22,23]	[220,230]	450
24	24	[23,24]	[230,240]	470
25	25	[24,25]	[240,250]	490
26	26	[25,26]	[250,260]	510
27	27	[26,27]	[260,270]	530
28	28	[27,28]	[270,280]	550
29	29	[28,29]	[280,290]	570
30	30	[29,30]	[290,300]	590
31	31	[30,31]	[300,310]	610
32	32	[31,32]	[310,320]	630
33	33	[32,33]	[320,330]	650
34	34	[33,34]	[330,340]	670
35	35	[34,35]	[340,350]	690
36	36	[35,36]	[350,360]	710
37	37	[36,37]	[360,370]	730
38	38	[37,38]	[370,380]	750
39	39	[38,39]	[380,390]	770
40	40	[39,40]	[390,400]	790
Exit	-	-	-	All rows processed

💡 All rows processed, window function completed

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
Current Row (id)	1	2	3	4	5	6	40
Frame Rows Included (id)	[1]	[1,2]	[2,3]	[3,4]	[4,5]	[5,6]	[39,40]
SUM(value)	10	30	50	70	90	110	790

Key Moments - 3 Insights

Why does the first row only include itself in the frame?

Because the frame is defined as '1 PRECEDING AND CURRENT ROW', and for the first row there is no preceding row, so only the current row is included (see execution_table row 1).

How does the frame move as we go to the next rows?

The frame shifts forward by including the current row and the one before it, dropping the oldest row outside the frame (see execution_table rows 2 to 5).

What happens if there is no ORDER BY in the window function?

Without ORDER BY, the frame cannot be defined in terms of row order, so ROWS BETWEEN or RANGE BETWEEN cannot be applied meaningfully, resulting in an error or default frame (not shown here).

Visual Quiz - 3 Questions

Look at the execution_table at Step 3. Which rows are included in the frame?

A [1, 2, 3]

B [3]

C [2, 3]

D [1, 3]

💡 Hint

Check the 'Frame Rows Included (id)' column at Step 3 in the execution_table.

At which step does the SUM(value) first reach 90?

A Step 4

B Step 5

C Step 6

D Step 3

💡 Hint

Look at the 'SUM(value) Result' column in the execution_table to find when it equals 90.

If the frame was changed to 'ROWS BETWEEN 2 PRECEDING AND CURRENT ROW', how would the frame size change?

A It would include 3 rows per frame

B It would include only 1 row per frame

C It would include all rows in the partition

D It would include no rows

💡 Hint

Consider how '2 PRECEDING AND CURRENT ROW' expands the frame compared to '1 PRECEDING AND CURRENT ROW'.

Concept Snapshot

Window frames define which rows a window function uses for each row.
ROWS BETWEEN counts rows before/after current row.
RANGE BETWEEN uses value ranges in ORDER BY column.
Frames slide as query processes each row.
Use ORDER BY to define row order for frames.
Example: ROWS BETWEEN 1 PRECEDING AND CURRENT ROW sums current and previous row.

Full Transcript

Window frame clauses in PostgreSQL let you control which rows a window function looks at for each row. The process starts by optionally dividing data into partitions, then ordering rows. For each row, a frame is defined using ROWS BETWEEN or RANGE BETWEEN, specifying how many rows or value ranges before and after the current row to include. The window function then calculates its result over this frame. For example, using ROWS BETWEEN 1 PRECEDING AND CURRENT ROW sums the current row's value and the one before it. The frame moves forward as the query processes each row, producing a running calculation. Without ORDER BY, frames cannot be defined properly. This visual trace shows how the frame includes rows and how the sum changes step-by-step.

Practice

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

easy

A. It defines the frame by counting rows relative to the current row position.

B. It defines the frame by counting rows based on the value range in the ORDER BY column.

C. It filters rows before applying the window function.

D. It sorts the rows before applying the window function.

Solution

1. Step 1: Understand window frame types

   Window frames can be defined by position (ROWS) or by value range (RANGE).

2. Step 2: Identify what ROWS BETWEEN does

   ROWS BETWEEN counts rows relative to the current row's position, not by values.

3. Final Answer:

   It defines the frame by counting rows relative to the current row position. -> Option A

4. Quick Check:

   ROWS BETWEEN = position-based frame [OK]

Hint: ROWS counts rows by position, RANGE counts by value range [OK]

Common Mistakes:

• Confusing ROWS with RANGE
• Thinking ROWS filters rows
• Assuming ROWS sorts data

2. Which of the following is the correct syntax to specify a window frame using RANGE BETWEEN from 5 preceding to current row?

easy

A. RANGE BETWEEN 5 PRECEDING AND CURRENT ROW

B. RANGE BETWEEN CURRENT ROW AND 5 PRECEDING

C. ROWS BETWEEN 5 PRECEDING AND CURRENT ROW

D. ROWS BETWEEN CURRENT ROW AND 5 PRECEDING

Solution

1. Step 1: Recall RANGE BETWEEN syntax

   The syntax is: RANGE BETWEEN <start> AND <end>, where start must be before end logically.

2. Step 2: Check order of frame boundaries

   5 PRECEDING comes before CURRENT ROW, so 'RANGE BETWEEN 5 PRECEDING AND CURRENT ROW' is correct.

3. Final Answer:

   RANGE BETWEEN 5 PRECEDING AND CURRENT ROW -> Option A

4. Quick Check:

   Correct RANGE syntax = RANGE BETWEEN 5 PRECEDING AND CURRENT ROW [OK]

Hint: Frame boundaries go from earlier to later, e.g., 5 PRECEDING to CURRENT ROW [OK]

Common Mistakes:

• Reversing frame boundaries order
• Using ROWS syntax with RANGE
• Omitting BETWEEN keyword

3. Given the table sales(date, amount) ordered by date, what will the following query return?

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

medium

A. Sum of amounts for the current row and 1 row before

B. Sum of all amounts up to the current row

C. Sum of amounts for the current date and the previous day

D. Sum of amounts for the current date only

Solution

1. Step 1: Understand RANGE BETWEEN with INTERVAL

   RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW includes rows with date values within 1 day before current row's date.

2. Step 2: Interpret the SUM window function

   SUM(amount) will add amounts for rows dated from 1 day before up to current date.

3. Final Answer:

   Sum of amounts for the current date and the previous day -> Option C

4. Quick Check:

   RANGE with INTERVAL sums by date range [OK]

Hint: RANGE with INTERVAL sums values in date/time range [OK]

Common Mistakes:

• Confusing RANGE with ROWS counting
• Assuming ROWS behavior for RANGE
• Ignoring INTERVAL meaning

4. Identify the error in this window frame clause:

SUM(amount) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND 3 PRECEDING)

medium

A. The clause is syntactically correct.

B. ROWS cannot be used with ORDER BY date.

C. SUM cannot be used as a window function.

D. The frame boundaries are reversed; '3 PRECEDING' should come before 'CURRENT ROW'.

Solution

1. Step 1: Check frame boundary order

   Frame boundaries must go from earlier to later, so '3 PRECEDING' should be before 'CURRENT ROW'.

2. Step 2: Validate syntax correctness

   Using ROWS with ORDER BY date and SUM is valid, so no other syntax errors.

3. Final Answer:

   The frame boundaries are reversed; '3 PRECEDING' should come before 'CURRENT ROW'. -> Option D

4. Quick Check:

   Frame boundaries order matters [OK]

Hint: Frame start must be before frame end [OK]

Common Mistakes:

• Reversing frame boundaries
• Thinking ROWS can't use ORDER BY
• Misunderstanding window function usage

5. You want to calculate a moving average of sales amounts over the last 7 days including the current day, but your sales table has multiple sales per day. Which window frame clause correctly computes this using RANGE?

hard

A. RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW

B. RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW

C. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

D. ROWS BETWEEN 7 PRECEDING AND CURRENT ROW

Solution

1. Step 1: Understand the requirement

   We want a 7-day moving average including current day, so the frame covers 6 days before plus current day (total 7 days).

2. Step 2: Choose correct frame type

   Since multiple sales per day exist, using RANGE with INTERVAL ensures all rows within date range are included.

3. Step 3: Select correct interval

   INTERVAL '6 days' PRECEDING to CURRENT ROW covers 7 days total (6 before + current).

4. Final Answer:

   RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW -> Option B

5. Quick Check:

   Use RANGE with 6 days preceding for 7-day window [OK]

Hint: RANGE with INTERVAL covers value ranges, perfect for dates [OK]

Common Mistakes:

• Using ROWS which counts rows, not days
• Choosing wrong interval length
• Confusing 6 days preceding with 7 days