Window Frames (ROWS, RANGE, GROUPS) in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Aggregate Window Functions (SUM, AVG, COUNT OVER)
Builds toward Running Totals and Cumulative Metrics
What are Window Frames in SQL?
The window frame clause controls exactly which rows feed into a window function's calculation for each row. It's a sliding boundary that moves as the function processes the partition.
You've already used window functions that compute running totals: SUM(revenue) OVER (PARTITION BY region ORDER BY sale_date). What you may not have noticed is that a default frame is silently active. When you include ORDER BY inside OVER, the frame automatically covers from the start of the partition through the current row. That's why running totals accumulate correctly without any extra syntax.
Once you want a rolling window — the last 7 days, the last N rows, a specific date range — you need to declare the frame explicitly. ROWS and RANGE are the two modes you'll use in practice.
How do you write a rolling window with ROWS?
ROWS counts physical row positions. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means: the current row plus exactly the 6 rows before it in the sorted partition.
SUM(revenue) OVER (
PARTITION BY region
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)If the data has one row per day, this is a 7-day rolling sum. But if there are gaps in the date sequence — a day with no data — ROWS still grabs exactly 6 prior rows regardless of their dates. You'd be summing across a longer calendar window than you intended.
How does RANGE handle gaps in dates?
RANGE operates on values, not positions. RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW includes all rows whose date falls within the 6 calendar days before the current row's date.
SUM(revenue) OVER (
PARTITION BY region
ORDER BY sale_date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
)Gaps are handled correctly: missing days contribute nothing. The window always represents a true 7-day calendar range regardless of how many rows it contains.
With UNBOUNDED PRECEDING, ROWS and RANGE produce identical results on this data — you can confirm it:
SELECT ordered_at::date AS order_date, total_amount, SUM(total_amount) OVER (ORDER BY ordered_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_running, SUM(total_amount) OVER (ORDER BY ordered_at RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_running FROM orders ORDER BY ordered_at LIMIT 10
When do ROWS and RANGE give different answers?
The one thing that trips people up
ROWS and RANGE produce identical results when there are no gaps and one row per date. They diverge the moment gaps appear — and they diverge silently. Both queries run without error, but one returns the wrong values.
Use RANGE when gaps in the data matter and you want a true calendar window. Use ROWS when you need a fixed number of observations regardless of what dates they cover.
What does UNBOUNDED PRECEDING mean in a window frame?
UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
These boundary keywords cover the full partition. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING covers every row for every calculation. This is the frame you need for LAST_VALUE and NTH_VALUE to behave as expected, and for any aggregate that should look at the entire group rather than a sliding window.
GROUPS is a third frame mode that counts peer groups (rows with equal ORDER BY values) rather than positions or value ranges. It's less common and covered when you encounter tied values in ranked windows.
Practice Window Frames in SQL
Brightlane's revenue operations team wants a rolling 3-order spend total per customer to see short-horizon spending behavior.
Write a query to return every order's ID, customer ID, order amount, and the combined total_amount across that customer's current order plus the two immediately preceding orders chronologically.
Assumptions:
- Within each customer's orders, the rolling-3 sum at each row covers that order plus the two orders with the largest
ordered_atstrictly before it. The window is restricted to that customer. - For a customer's first order, the rolling-3 sum equals just that order's
total_amount. For a customer's second order, the sum covers two orders. From the third order onward, it covers three. - The final result is sorted by
customer_idascending, then byordered_atascending.
Output:
- One row per order, with columns
id,customer_id,total_amount, androlling_3_sum. Sorted bycustomer_id, thenordered_at.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 Window Frames practice problems
Write a query to return every order's ID, customer ID, order amount, and the combined total_amount across that customer's current order plus the two immediately preceding orders chronologically.
Write a query to return every session's ID, user ID, event count, and the average event_count across that user's current session plus the two immediately preceding sessions chronologically.
Write a query to return every order's ID, ordered-at timestamp, total amount, and the combined total_amount across that order plus the two orders placed immediately after it in the global chronological sequence.
Write a query to return every order's ID, ordered-at timestamp, total amount, and the combined total_amount across every order whose ordered_at falls within the 30 calendar days ending on and including that order's ordered_at.
Write a query to return every order's ID, customer ID, order amount, and the average total_amount across that customer's current order plus the six immediately preceding orders chronologically.
Write a query to return every delivered order's ID, customer ID, amount, and the average total_amount across that customer's current delivered order plus the two immediately preceding delivered orders chronologically.
Write a query to return every order's ID, customer ID, order amount, and the difference between that order's total_amount and the average across that customer's current order plus the two immediately preceding orders chronologically.
Write a query to return every order's ID, ordered-at timestamp, total amount, the combined total_amount across that order plus up to the two immediately preceding orders chronologically, and the count of orders included in that sum.
Write a query to return every order's ID, ordered-at timestamp, total amount, the position-based rolling-3 sum, and the 30-day calendar rolling sum.
Start learning to practice all 9 Window Frames problems, with instant grading and mastery tracking.
Common questions about Window Frames
What is the default frame when you write ORDER BY inside OVER?
Everything from the start of the partition to the current row, compared by value rather than by position. That default is why a plain running total accumulates correctly without any frame clause written out.
Is there a frame at all when OVER has no ORDER BY?
The whole partition is in view, which is why an aggregate with a bare OVER clause gives every row the same figure. There is nothing to accumulate along, so there is nothing for a frame to slide over.
What does GROUPS mode do?
It counts peer groups rather than rows or values, so one step back means the previous set of tied rows however many there are. It is the least used of the three and worth knowing mainly so that an unfamiliar frame clause does not stop you reading a query.
