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 ROWLimit the Scope? - 8.
ROWSvsRANGE - 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:
| turn | weight |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
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:
| turn | weight |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
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:
| turn | weight | running_total |
|---|---|---|
| 1 | 10 | 10 |
| 2 | 20 | 30 |
| 3 | 30 | 60 |
| 4 | 40 | 100 |
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:
| turn | weight |
|---|---|
| 1 | 10 |
| 1 | 20 |
| 2 | 30 |
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_nameweightturn
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:
| person_name | weight | turn |
|---|---|---|
| Alice | 250 | 1 |
| Bob | 300 | 2 |
| Charlie | 400 | 3 |
| David | 200 | 4 |
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:
| person_name | weight | turn | sum |
|---|---|---|---|
| Alice | 250 | 1 | 250 |
| Bob | 300 | 2 | 550 |
| Charlie | 400 | 3 | 950 |
| David | 200 | 4 | 1150 |
Now the problem is much easier.
13. Remove Anyone Who Exceeds the Limit
We can simply use:
WHERE sum <= 1000
This leaves:
| person_name | sum |
|---|---|
| Alice | 250 |
| Bob | 550 |
| Charlie | 950 |
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 ROWis 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
| Syntax | Meaning |
|---|---|
UNBOUNDED PRECEDING |
Start at the first row |
CURRENT ROW |
End at the current row |
UNBOUNDED FOLLOWING |
Extend to the last row |
ROWS |
Frame based on physical row positions |
RANGE |
Frame based on ordering values and their peers |
ROWS/RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
Running/cumulative frame |
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.
Top comments (0)