The OVER clause can look complicated because it answers three separate questions. Which rows belong together? In what order should they be considered? Which of those rows should contribute to the value on the current row? Those questions correspond to the partition, window order and frame.
A partition might contain all purchases from one customer. The order might place them by purchase time. The frame might include only that customer's purchases up to the current purchase. Once you separate those choices, running totals and rolling calculations become much easier to explain. This reference extends the original window lesson with the boundaries that are easy to overlook.
Partitions are independent groups
PARTITION BY customer_id puts each customer's records into a separate calculation group. Window functions then operate within that group. Unlike GROUP BY, the partition does not reduce all of a customer's records to one output row. Each input row can remain visible with a customer-specific calculation beside it.
Omitting PARTITION BY creates one partition containing the whole query input. That is appropriate for an overall company total. Adding a month to the partition resets the calculation every month, which is appropriate only if the report calls for a monthly reset.
The input is already affected by joins, WHERE filtering and grouping. A window does not automatically see records removed earlier. If a report needs the full-history running balance but should display only recent rows, calculate the balance before applying the final date filter. Otherwise the opening balance silently starts at the display boundary.
Frame boundaries in ordinary language
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING includes the previous record, current record and next record, where those records exist. At a partition boundary there are fewer available records. A frame cannot cross into another partition to complete its requested size.
UNBOUNDED PRECEDING reaches the start of the partition. UNBOUNDED FOLLOWING reaches its end. The full-partition frame can be useful when every row should show the same total alongside its own detail. Without a window order, a partitioned aggregate also commonly calculates the whole partition.
RANGE interprets boundaries using ordering values and peer groups rather than individual row positions. Offset ranges are particularly dialect-sensitive: accepted data types, intervals and multiple ordering columns vary. Do not assume a syntax for “seven preceding days” can be pasted unchanged into every database.
Some engines also support GROUPS, which counts peer groups. SQLite and PostgreSQL document it, but it is not a universal option across the databases in this course. Learn the common row-frame behavior first, then use a more specialized frame when the question specifically requires it.
Default frames and LAST_VALUE
An ordered aggregate often has a default frame ending at the current row and its peers. That means adding an ORDER BY can turn a full-partition total into a cumulative total without changing SUM itself. Writing the intended frame explicitly avoids relying on a default that a reader may misremember.
LAST_VALUE is another frequent surprise. If its frame ends at the current row, it returns the last value in that frame, which is often the current row's value rather than the partition's final observation. For the final value across the partition, specify a full frame ending with UNBOUNDED FOLLOWING.
Ranking functions and offset functions have their own semantics. A frame declaration does not make ROW_NUMBER count only nearby rows or make LAG search only inside an aggregate frame. Check the specific function rather than assuming all window functions consume frames in the same way.
The PostgreSQL window reference explains this relationship between peers, frames and functions. The SQLite frame reference supplies additional frame examples for practice.
Practice: state the calculation before the query
Write two report requirements for the three sample rows. The first says “show the total after each individual sale.” The second says “show the total through the end of this sale's day.” The row frame with a unique sale order answers the first; the peer-aware range frame answers the second.
Now add a customer ID and a second customer's sale on day one. Both queries should partition by customer if the reports concern customer spending. Confirm that each customer's first total contains only that customer's money. The final display can still list all customers together without changing the independent calculation.
For a third report, request each customer's final total on every row. Use a full-partition aggregate instead of a running frame. Label that column “customer total,” because displaying it beside an individual sale does not make it the sale amount. Clear column labels prevent correctly calculated numbers from being interpreted incorrectly.
For a balance carried into a reporting period, keep the older rows available to the calculation even if they will not appear in the final display. An outer query can remove those older rows after the full-history calculation. Test the first displayed balance against a manually calculated opening balance.
SQL Window Frames and PARTITION BY Examples
Each example shows the code, the output it produces and what each part does.
ROWS and RANGE with peers
These rows include two records with the same day number. PostgreSQL, MySQL 8+, SQL Server and modern SQLite support the frame forms shown here.
The row frame follows individual records in a unique order. The range frame treats equal day values as peers, so both day-one records receive the total for that peer group. The first total of 30 is deliberate behavior, not an incorrect sum.
CREATE TABLE frame_sales (
sale_id INTEGER PRIMARY KEY,
day_number INTEGER NOT NULL,
amount INTEGER NOT NULL
);
INSERT INTO frame_sales VALUES
(1, 1, 10), (2, 1, 20), (3, 2, 30);
SELECT sale_id, day_number, amount,
SUM(amount) OVER (
ORDER BY day_number, sale_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS row_total,
SUM(amount) OVER (
ORDER BY day_number
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS peer_total
FROM frame_sales
ORDER BY sale_id;sale_id | day_number | amount | row_total | peer_total
1 | 1 | 10 | 10 | 30
2 | 1 | 20 | 30 | 30
3 | 2 | 30 | 60 | 60SQL Window Frames and PARTITION BY FAQ
Does PARTITION BY sort the output?
No. It groups rows for window calculations. Use the outer query's ORDER BY to control display order.
Can a frame include rows from another partition?
No. Partition boundaries constrain every frame associated with that window, so a three-row moving average at the start of a store's data uses only that store's first rows, never the end of the previous store.
Should I always use ROWS?
Use it when row positions express the intended calculation. Use peer-aware or value-based frames when ordering values, rather than individual records, define the boundary.
Why does LAST_VALUE return the current row's value?
With ORDER BY in the window and no frame clause, the default frame ends at the current row, so the "last" row in the frame is the current one. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to make LAST_VALUE see the whole partition.
Can several window functions share one definition?
Yes. Name it once with a WINDOW clause, WINDOW w AS (PARTITION BY store_id ORDER BY day), and write OVER w in each function. PostgreSQL, MySQL 8 and SQLite support this; SQL Server added it in 2022. It keeps long queries consistent.
SQL Window Frames and PARTITION BY Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Two sales share the same day. The report needs a total after each individual sale. Which design is clearest?
SQL Window Frames and PARTITION BY Quiz
2 questions to check what you learned.
What does PARTITION BY customer_id change?
- Each customer's window calculation uses an independent group of rows
- It combines every customer into one stored row
- It changes customer IDs permanently
Why can LAST_VALUE return the current row's value?
- LAST_VALUE always ignores ordering
- Its active frame may end at the current row
- It always reads the last inserted record