SQL Window Frames Explained: How UNBOUNDED PRECEDING Creates a Running Total

If you have started learning SQL window functions, you have probably seen something like this:

SUM(weight) OVER(
    ORDER BY turn
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

At first glance, the syntax looks intimidating.

What exactly is a frame?

What does UNBOUNDED PRECEDING mean?

Why does CURRENT ROW not mean that the calculation only considers the current row?

And how does this produce:

10
30
60
100

instead of just giving the total 100 everywhere?

I had the same confusion, so let's break it down from first principles and then use LeetCode 1204 — Last Person to Fit in the Bus as a practical example.

Table of Contents

• 1. First: What Is a Window Function?
• 2. So What Is a Window?
• 3. What Is a Window Frame?
• 4. Understanding UNBOUNDED PRECEDING
• 5. Understanding CURRENT ROW
• 6. Putting It Together
• 7. Why Doesn't CURRENT ROW Limit the Scope?
• 8. ROWS vs RANGE
• 9. What Happens If We Don't Write the Frame?
• 10. Not Every Window Function Uses the Frame
• 11. A Real Problem: LeetCode 1204
• 12. Building the Running Total
• 13. Remove Anyone Who Exceeds the Limit
• 14. Using FIRST_VALUE()
• 15. Complete Solution
• 16. The Mental Model I Use for Window Frames
• 17. Other Useful Window Frames
• 18. Final Takeaway
• Quick Reference
• References

1. First: What Is a Window Function?

A normal aggregate function such as:

SUM(weight)

combines multiple rows into a single result.

For example:

weight
------
10
20
30
40

A normal SUM() gives:

100

The individual rows are no longer represented in the result of that aggregation.

A window function is different.

When we write:

SUM(weight) OVER(...)

SQL calculates a sum using a set of related rows, but keeps the original rows.

So instead of:

100

we can get something like:

10
30
60
100

That is the first important idea:

A window function performs a calculation across related rows without collapsing those rows into one row.

2. So What Is a Window?

Consider:

SUM(weight) OVER(
    ORDER BY turn
)

There are several pieces here.

SUM(weight)

This tells SQL what calculation to perform.

OVER(...)

turns the calculation into a window function.

ORDER BY turn

tells SQL how the rows should be ordered inside that window.

But there is one more concept:

Which rows should actually be included for the current row?

That is where the window frame comes in.

3. What Is a Window Frame?

A frame is a subset of the rows in the current window/partition that the function operates on for the current row.

Think of it as a moving boundary.

For example, suppose we have:

If we want a running total, we want the frame to behave like this:

For turn 1:

[10]


For turn 2:

[10, 20]


For turn 3:

[10, 20, 30]


For turn 4:

[10, 20, 30, 40]

Therefore the results are:

10
30
60
100

The frame is what lets us describe this behavior.

4. Understanding UNBOUNDED PRECEDING

Now let's look at:

UNBOUNDED PRECEDING

This means:

Start at the first row of the partition.

It does not mean "some extremely large number of rows before the current row."

It literally means that the frame begins at the beginning of the partition.

So:

UNBOUNDED PRECEDING

is essentially saying:

Start here
↓
[ FIRST ROW ------------------------ CURRENT ROW ]

For our example:

Turn 1:
[10]

Turn 2:
[10, 20]

Turn 3:
[10, 20, 30]

Turn 4:
[10, 20, 30, 40]

5. Understanding CURRENT ROW

Now comes the part that initially seems confusing.

We have:

CURRENT ROW

Does that mean the frame contains only the current row?

No.

It means:

The end boundary of the frame is the current row.

So when we write:

BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

we are saying:

Start at the beginning and stop at the current row.

Therefore:

Current row = 1

[1]


Current row = 2

[1, 2]


Current row = 3

[1, 2, 3]


Current row = 4

[1, 2, 3, 4]

That's why it creates a running total.

6. Putting It Together

Now the entire expression:

SUM(weight) OVER(
    ORDER BY turn
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

can be translated into plain English as:

Order the rows by turn, and for each row, sum everything from the beginning of the window up to the current row.

For:

the calculation is:

Turn 1:
10
= 10


Turn 2:
10 + 20
= 30


Turn 3:
10 + 20 + 30
= 60


Turn 4:
10 + 20 + 30 + 40
= 100

Result:

That is a running total.

7. Why Doesn't CURRENT ROW Limit the Scope?

This is the most common source of confusion.

When you see:

BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

don't read it as:

"Use the current row."

Read it as:

"The frame starts at the beginning and its ending boundary is the current row."

The frame is therefore different for every row.

Row 1:
[1]


Row 2:
[1, 2]


Row 3:
[1, 2, 3]


Row 4:
[1, 2, 3, 4]

The current row moves, and the frame moves with it.

That's the key mental model:

A window frame is evaluated relative to each current row.

8. ROWS vs RANGE

There are two frame units that are particularly important in MySQL:

ROWS

and

RANGE

They can look similar, but they are conceptually different.

ROWS

ROWS works with physical row positions.

For example:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

means:

Include every physical row from the first row through the current row.

RANGE

RANGE works with values in the window ordering, so rows that are peers according to the ORDER BY can belong to the same frame boundary.

For example, if we have:

then with:

ORDER BY turn
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

the two rows with turn = 1 are peers.

So the frame for the turn = 1 rows includes both of them.

This distinction matters whenever your ORDER BY column contains duplicate values.

For a simple row-by-row running total where the ordering column is unique, ROWS is often the clearest way to express the intention:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

9. What Happens If We Don't Write the Frame?

This is another important point.

In MySQL, when an ORDER BY is present and no explicit frame is specified, the default frame is equivalent to:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

So:

SUM(weight) OVER(
    ORDER BY turn
)

is effectively using that default frame.

However, writing the frame explicitly can be useful when you're learning or when you want the query to communicate exactly which rows should be included.

It also makes the distinction between RANGE and ROWS explicit.

10. Not Every Window Function Uses the Frame

This is an extremely important distinction.

Functions such as:

SUM()
AVG()
FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()

can operate on the rows in the current frame.

But functions such as:

ROW_NUMBER()
RANK()
DENSE_RANK()
LAG()
LEAD()

do not use the frame in the same way.

For example:

ROW_NUMBER() OVER(
    ORDER BY turn
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

does not suddenly make ROW_NUMBER() calculate a running count based on that frame.

ROW_NUMBER() is concerned with the position of the current row in the ordered partition.

This is why it is useful to distinguish between:

window ordering

and

window framing

They are related, but they are not the same concept.

11. A Real Problem: LeetCode 1204

Now let's use this idea in an actual SQL problem.

The problem gives us people waiting to enter a bus.

Each person has:

• person_name
• weight
• turn

The bus can carry a maximum weight of 1000.

We need to find:

The last person who can get on the bus without making the total weight exceed 1000.

The queue order is determined by turn.

So the first thing we need is:

Person 1
Person 1 + Person 2
Person 1 + Person 2 + Person 3
...

In other words:

We need a running total.

12. Building the Running Total

This is the key part of the solution:

SUM(weight) OVER(
    ORDER BY turn ASC
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS sum

Suppose the queue is:

The window frame produces:

Turn 1:
[250]
→ 250


Turn 2:
[250, 300]
→ 550


Turn 3:
[250, 300, 400]
→ 950


Turn 4:
[250, 300, 400, 200]
→ 1150

So our derived table becomes:

Now the problem is much easier.

13. Remove Anyone Who Exceeds the Limit

We can simply use:

WHERE sum <= 1000

This leaves:

David is excluded because:

1150 > 1000

Now the answer must be the person with the largest remaining cumulative total.

14. Using FIRST VALUE

This is where I use:

FIRST_VALUE(person_name) OVER(ORDER BY sum DESC)

After filtering, the rows are ordered by cumulative weight:

Charlie → 950
Bob     → 550
Alice   → 250

Therefore, the first value is:

Charlie

FIRST_VALUE() returns that first value from the current frame.

Since the first value is the same for all the remaining rows, the intermediate result is:

Charlie
Charlie
Charlie

So I use:

DISTINCT

to get:

Charlie

15. Complete Solution

Here is the complete query:

SELECT 

    DISTINCT FIRST_VALUE(person_name) OVER(ORDER BY sum DESC) AS 'person_name'

FROM (

    SELECT *, 

        SUM(weight) OVER(
            ORDER BY turn ASC 
            RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS 'sum'

    FROM Queue

) x 

WHERE sum <= 1000;

The solution can be viewed as four steps:

Queue
   ↓
Calculate running total
   ↓
Remove totals > 1000
   ↓
Find the person with the largest valid total

16. The Mental Model I Use for Window Frames

When I see:

ROWS/RANGE BETWEEN ... AND ...

I try not to memorize the syntax.

Instead, I ask two questions:

Where does the frame start?

For example:

UNBOUNDED PRECEDING

means:

Start from the beginning.

Where does the frame end?

For example:

CURRENT ROW

means:

Stop at the current row.

So:

BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

becomes:

START
 ↓
[---------------- CURRENT ROW]

As the current row changes:

Row 1:
[1]


Row 2:
[1, 2]


Row 3:
[1, 2, 3]


Row 4:
[1, 2, 3, 4]

That's the mental model.

Once you visualize the frame, the syntax becomes much less intimidating.

17. Other Useful Window Frames

The same idea can be used for many other problems.

Running total

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

1
1 + 2
1 + 2 + 3
1 + 2 + 3 + 4

Moving 3-row average

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

For example:

Row 1 → [1]
Row 2 → [1, 2]
Row 3 → [1, 2, 3]
Row 4 → [2, 3, 4]
Row 5 → [3, 4, 5]

This is useful for rolling/moving calculations.

Entire partition

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

The frame contains the entire partition for every row.

18. Final Takeaway

The biggest thing I learned from window frames is that:

CURRENT ROW is a boundary, not a restriction to a single row.

When we write:

BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

we are saying:

Start from the beginning of the partition and extend the frame through the current row.

That is why:

SUM(weight) OVER(
    ORDER BY turn
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

creates a running total.

The frame changes for every current row:

[1]
[1, 2]
[1, 2, 3]
[1, 2, 3, 4]
...

Once this mental model clicks, many window-function problems become much easier to reason about.

Quick Reference

If you're learning SQL window functions, don't just memorize:

UNBOUNDED PRECEDING

Visualize the frame moving through the partition.

That is the part that makes the syntax finally make sense.

References

• MySQL 8.0 Reference Manual — Window Function Frame Specification
• MySQL 8.0 Reference Manual — Window Function Descriptions