SQL PARTITION BY Demystified: The Hidden Mechanics of Window Frames
3 min read

When performing aggregations and analysis in SQL, window functions often appear alongside
GROUP BY. In particular,PARTITION BYallows you to divide data by specific columns and perform calculations per partition, and it works fundamentally differently from standardGROUP BY.
Schema: sales table
The
salestable contains sales information:
-- sales Table
+--+----+----------+------+
|id|type|month |amount|
+--+----+----------+------+
|1 |A |2025-01-01|100 |
|2 |A |2025-02-01|150 |
|3 |A |2025-03-01|120 |
|4 |A |2025-04-01|180 |
|5 |B |2025-01-01|90 |
|6 |B |2025-02-01|130 |
|7 |B |2025-03-01|200 |
|8 |B |2025-04-01|170 |
+--+----+----------+------+
- id: unique row identifier
- type: product type
- month: sales month
- amount: sales amount
PARTITION BY
PARTITION BYis used in window functions to divide data by a column so that calculations can be performed for each partition.
How it works
SELECT
s.month,
SUM(s.amount) OVER (PARTITION BY month) AS total_amount
FROM sales AS s;
-- Result
+----------+------------+
|month |total_amount|
+----------+------------+
|2025-01-01|190 |
|2025-01-01|190 |
|2025-02-01|280 |
|2025-02-01|280 |
|2025-03-01|320 |
|2025-03-01|320 |
|2025-04-01|350 |
|2025-04-01|350 |
+----------+------------+
PARTITION BY monthdivides the data bymonthand repeats the total within each partition.- Important: All rows are preserved; no grouping is performed.
In contrast, GROUP BY combines rows into a single row per group, and individual row details are lost:
-- Group By Result
| month | total_amount |
| ---------- | ------------ |
| 2025-01-01 | 190 |
| 2025-02-01 | 280 |
| 2025-03-01 | 320 |
| 2025-04-01 | 350 |
Syntax and Window Frames
Although not explicitly shown,
PARTITION BYcan be combined with the ROWS/RANGE window frames:
<window_function> OVER (
[PARTITION BY ...]
[ORDER BY ...]
{ ROWS | RANGE | GROUPS } BETWEEN <frame_start> AND <frame_end>
)
- PARTITION BY: grouping unit
- ORDER BY: frame ordering
- ROWS / RANGE / GROUPS: frame type
- frame_start / frame_end: frame boundaries
Frame options:
UNBOUNDED PRECEDING: from the first row of the partitionn PRECEDING: n rows before the current rowCURRENT ROW: current rown FOLLOWING: n rows after the current rowUNBOUNDED FOLLOWING: until the last row of the partition
PARTITION BY: Window Frame
In window functions, you can define a frame to specify the range of rows over which calculations are performed. A frame can be one of ROWS, RANGE, or GROUPS, and calculations are performed according to the partition and ordering criteria.
SELECT s.month,
SUM(s.amount) OVER (PARTITION BY month
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS total_amount
FROM sales AS s
This query calculates total_amount as the sum from the first row of each month frame up to the current row.
However, there is a subtle point. If you divide the sales table by month and sum from the first row up to the current row, you might expect the following result:
-- sales table
+--+----+----------+------+
|id|type|month |amount|
+--+----+----------+------+
|1 |A |2025-01-01|100 |
...
|5 |B |2025-01-01|90 |
|8 |B |2025-04-01|170 |
+--+----+----------+------+
-- Query
SELECT s.month,
SUM(s.amount) OVER (PARTITION BY month
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_amount
FROM sales AS s
-- Result
+----------+------------+
|month |total_amount|
+----------+------------+
|2025-01-01|100 |
|2025-01-01|190 |
+----------+------------+
PARTITION BY: ROWS, RANGE
- ROWS calculates row by row, so 100 and 190 are computed separately even though they have the same month.
- RANGE treats rows with the same value in the window frame as a single unit. For example, both 100 and 90 are considered as one, so
total_amount = 190for both rows.
Example: Implementing LAG
The
LAGfunction retrieves the value of a column from a previous row:
LAG(<column>, <offset>, <default_value>)
OVER (PARTITION BY <partition_column> ORDER BY <order_column>)
You can achieve this without LAG by using a window frame:
SELECT
id,
type,
month,
amount,
SUM(amount) OVER (
PARTITION BY type
ORDER BY month
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS prev_amount
FROM sales;
-- Result
+--+----+----------+------+-----------+
|id|type|month |amount|prev_amount|
+--+----+----------+------+-----------+
|1 |A |2025-01-01|100 |null |
|2 |A |2025-02-01|150 |100 |
|3 |A |2025-03-01|120 |150 |
|4 |A |2025-04-01|180 |120 |
|5 |B |2025-01-01|90 |null |
|6 |B |2025-02-01|130 |90 |
|7 |B |2025-03-01|200 |130 |
|8 |B |2025-04-01|170 |200 |
+--+----+----------+------+-----------+
- The window frame is set to reference only 1 row preceding (
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING). - For the first row,
NULLis returned because there is no previous row. - Calculations are independent per partition (
type).
By leveraging this approach, you can customize queries to perform various row-level calculations using window frames.
