SQL Tutorial
•
•
4 min read
SQL Window Frame Clauses: ROWS vs RANGE (With Practice Examples)
Understanding Window Frame Clauses in SQL – ROWS vs RANGE (With Simple Examples)
Hey, I’m Gaurav.
When I first learned window functions, I was comfortable with `PARTITION BY` and `ORDER BY`.
But then I saw this:
```sql
SUM(salary) OVER (PARTITION BY department ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
```
And I got completely lost.
What is `ROWS BETWEEN`?
What does `UNBOUNDED PRECEDING` mean?
Why do we need a "frame"?
Today, I’ll explain Window Frame Clauses from the very beginning — exactly how I finally understood them.
We’ll use simple examples you can practice live on [sqlpractice.in](https://sqlpractice.in).
---
What is a Window Frame?
A window frame defines which rows should be included in the calculation for the current row.
Think of it like this:
> “For the current row, which previous or following rows should I consider when calculating SUM, AVG, MIN, MAX, etc.?”
By default, when you write `ORDER BY` in a window function, SQL applies a default frame. But most of the time, we need to define it explicitly for correct results.
There are two main ways to define the frame:
1. `ROWS` → Physical rows (count-based)
2. `RANGE` → Logical range (value-based)
---
1. ROWS BETWEEN (Most Commonly Used)
`ROWS` treats rows as physical positions.
Most Important Frame Types:
a) Running / Cumulative Total (Most Common)
```sql
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
```
Meaning:
Include all rows from the beginning of the partition up to the current row.
This is the standard way to calculate running total.
Practice Question:
Show each order with its amount and the running total per customer.
[Try it live →](https://sqlpractice.in/?question=running-total)
b) Moving Average / Rolling Window
```sql
AVG(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
```
Meaning:
Take the current row + previous 2 rows = 3-row moving average.
Practice Question:
Calculate 3-order moving average of amount per customer.
[Try it live →](https://sqlpractice.in/?question=moving-average)
c) Only Current Row
```sql
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN CURRENT ROW AND CURRENT ROW
)
```
d) Future Rows (Rare)
```sql
LEAD(amount, 1) OVER (...) -- or
SUM(amount) OVER (... ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
```
---
2. RANGE BETWEEN (Logical Range)
`RANGE` works on values, not physical row count.
It is useful when you want to include rows with the same value.
Example:
```sql
SUM(salary) OVER (
PARTITION BY department
ORDER BY salary
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
```
Difference between ROWS and RANGE:
| Clause | Based On | Handles Ties | Use Case |
| ROWS | Physical rows | Treats ties separately | Running totals, moving avg |
| RANGE | Actual values | Groups same values | When values are same |
Note: `RANGE` is less commonly used and not supported by all databases with complex frames.
---
How to Think About Window Frames (My Mental Model)
Whenever you write a window function with `ORDER BY`, ask yourself:
1. Do I want a running calculation?
→ Use `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`
2. Do I want a moving window of last N rows?
→ Use `ROWS BETWEEN N PRECEDING AND CURRENT ROW`
3. Do I want to include only the current row?
→ `ROWS BETWEEN CURRENT ROW AND CURRENT ROW`
4. Do I want to look into the future?
→ `ROWS BETWEEN CURRENT ROW AND N FOLLOWING`
Golden Rule:
If you're calculating cumulative / running values → always explicitly write the frame.
Default behavior can be confusing and database-dependent.
---
Common Interview Questions on Window Frame
1. What is the difference between `ROWS` and `RANGE`?
2. Write a query to calculate running total.
3. What is the default frame when you only write `ORDER BY`?
4. How would you calculate a 7-day moving average?
Practice Questions (Recommended)
- Running Total per customer
- 3-order moving average
- Compare current order with average of last 5 orders
- Cumulative count of orders per customer
You can find all these in the Window Functions Pack on sqlpractice.in.
---
Final Thoughts
Window Frame Clauses were the missing piece for me.
Once I understood that the frame defines "which rows participate in the calculation for the current row", everything clicked.
Start with `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` for running totals — it’s the most useful one.
The more you practice, the more natural it becomes to decide the right frame.
Go try these examples on [sqlpractice.in](https://sqlpractice.in) — especially the running total and moving average ones.
You’ll start seeing window functions as a powerful tool rather than a scary topic.
Keep practicing — one concept at a time!
Building sqlpractice.in to help everyone master SQL for interviews — completely free.