The idea
GROUP BY answers a question about a group and gives you one row for the group. A window
function answers the same kind of question but puts the answer on every row of the group. It
looks at a set of related rows, computes something, and leaves the rows where they are.
The OVER (…) clause defines that set of rows, the window. Three parts do the work: PARTITION BY
splits the rows into groups, ORDER BY puts each group in sequence, and the frame says which of
those rows are in view for the current row.
Figure 1
GROUP BY folds rows together; OVER keeps every row
| order_item_id | order_id | line_total |
|---|---|---|
| 139 | 1049 | 378.00 |
| 140 | 1049 | 107.98 |
| 141 | 1049 | 1137.00 |
| 601 | 1231 | 93.56 |
| 602 | 1231 | 294.30 |
| order_id | order_total |
|---|---|
| 1049 | 1622.98 |
| 1231 | 387.86 |
| order_item_id | line_total | order_total |
|---|---|---|
| 139 | 378.00 | 1622.98 |
| 140 | 107.98 | 1622.98 |
| 141 | 1137.00 | 1622.98 |
| 601 | 93.56 | 387.86 |
| 602 | 294.30 | 387.86 |
- Five order lines that belong to two orders. We want each order's total.
GROUP BY order_idfolds each order's lines into one row. The totals are right, but the line details are gone: five rows became two.OVER (PARTITION BY order_id)also groups by order, but only to compute the sum. Order 1049's partition is its three lines, which add up to 1,622.98.- The answer is written back onto every row of the partition. All five rows survive, so you can still show the line, its order total and, in one more step, the line's share.
Why it exists
Some questions need both the detail and the group. "Each product next to the best price in its
category." "Each order line as a share of its order." "This month's orders next to last month's."
With GROUP BY alone you aggregate in one query and join the result back to the detail in another.
That works, but it is long, and it is easy to get the join key wrong.
Other questions are about order, which GROUP BY ignores: running totals, rankings, "the customer's
second order", the gap between one order and the previous one. A window function does these in one
pass over the rows, which is why analyst interviews lean on them.
How it works under the hood
For every row the database does the same four things.
- Partition. Find the rows in the same
PARTITION BYgroup as this row. NoPARTITION BYmeans the whole result is one partition. - Order. Sort that partition by the window's
ORDER BY. - Frame. Choose which rows around the current one are in view.
- Compute. Run the function over the framed rows and write the answer on the current row.
The frame is where most of the power, and most of the confusion, lives.
Figure 2
The frame slides down the ordered rows
| month | orders | running_total | avg_3_months |
|---|---|---|---|
| 2023-01 | 192 | 192 | 192.0 |
| 2023-02 | 157 | 349 | 174.5 |
| 2023-03 | 191 | 540 | 180.0 |
| 2023-04 | 216 | 756 | 188.0 |
| 2023-05 | 218 | 974 | 208.3 |
| 2023-06 | 189 | 1163 | 207.7 |
- Months in order. For each row the window decides which rows are in view, the frame, and the function works only on those.
- Running total:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. At March the frame is January to March, and 192 + 157 + 191 = 540. - One row later, the same frame has gained April: 540 + 216 = 756. The start stays fixed and the end moves with the current row.
- Moving average:
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. At May the frame is only March, April and May, and (191 + 216 + 218) / 3 = 208.3. - At June the frame slides forward: February drops out, June comes in. Both ends move, so the window keeps its size of three rows.
If the window has an ORDER BY and no explicit frame, the default frame runs from the start of
the partition to the current row (and includes rows tied with it). That is why SUM(x) OVER (ORDER BY month) gives a running total, while SUM(x) OVER () gives the grand total on every row.
For a moving average, write the frame yourself: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is a
7-row window. Note that it counts rows, not days, so a missing day silently stretches the window.
Percentages follow naturally. A line's share of its order is line_total / SUM(line_total) OVER (PARTITION BY order_id): the detail on top, the partition total underneath, no join back needed.
Three families of function
Aggregates over a window: SUM, AVG, COUNT, MIN, MAX. Same functions you know, now
framed.
Ranking: ROW_NUMBER, RANK, DENSE_RANK, NTILE. They depend only on the order, and ties
are what separates them.
Figure 3
Three rankings, one tie
| name | unit_price | row_number | rank | dense_rank |
|---|---|---|---|---|
| Pacifica Black Metal Table Lamp | 409.00 | 1 | 1 | 1 |
| Fern Rattan Desk Lamp | 379.00 | 2 | 2 | 2 |
| Marlow Glass Wall Sconce | 349.00 | 3 | 3 | 3 |
| Ashford Linen Pendant Light | 349.00 | 4 | 3 | 3 |
| Quarry Ceramic Picture Light | 329.00 | 5 | 5 | 4 |
- Five Lighting products sorted by price. Two of them, the sconce and the pendant light, cost exactly $349.
ROW_NUMBERnever ties. It numbers rows 1 to 5, and which tied product gets 3 and which gets 4 is arbitrary unless you add a tiebreaker toORDER BY.RANKgives tied rows the same number and then skips: 3, 3, then 5. A "top 3" withRANKreturns four products here.DENSE_RANKgives tied rows the same number and does not skip: 3, 3, then 4. Pick by what the question says about ties.
Navigation: LAG and LEAD read a value from the previous or next row of the partition.
LAG(revenue) OVER (ORDER BY month) is last month's revenue next to this month's, and the first
month gets NULL because there is nothing before it. FIRST_VALUE and LAST_VALUE read the edges
of the frame.
Reading a window in one sentence
Say the OVER clause out loud and it becomes a sentence. SUM(line_total) OVER (PARTITION BY order_id) reads "add up line totals, within each order". LAG(orders) OVER (PARTITION BY channel ORDER BY month) reads "the previous month's orders, within each channel". If you cannot say who
the "within each" is, the window is missing a PARTITION BY, and the numbers will blend groups that
should stay apart.
Why the frame is a row count, not a time span
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means "this row and the two before it", whatever they
contain. In the diagram that is three months only because every month has a row. If a month had no
orders and no row at all, the same frame would reach back four months and quietly average the wrong
period. The usual fix is to build a complete calendar first, with a row for every month or day, and
join your data onto it before applying the window.
Where the window runs
Window functions run after WHERE, GROUP BY and HAVING, and before SELECT's final ORDER BY
and LIMIT. Two consequences. You cannot filter on a window result in the same query's WHERE, so
"top 3 per category" is a CTE or subquery that computes ROW_NUMBER and an outer query that keeps
rn <= 3. And a window over an aggregated query sees the grouped rows: SUM(SUM(revenue)) OVER (ORDER BY month) is a running total of monthly totals.
Common misconceptions
"OVER (PARTITION BY x) is just GROUP BY x"
The grouping is the same, the output is not. GROUP BY returns one row per group. A window returns
every input row. If your result has fewer rows than you expected, something collapsed them; if it
has the same number, your window left them alone.
"LAG gets the previous row, so it works without PARTITION BY"
It gets the previous row in the window's order, across everything in the partition. With several
customers in one result and no PARTITION BY customer_id, a customer's first order is compared with
the last order of the previous customer. Whenever you compare to "before", ask before-for-whom.
"Ties don't matter if I use ROW_NUMBER"
They matter because ROW_NUMBER hides them: it breaks a tie arbitrarily, and the result can change
between runs. Add a tiebreaker column to the ORDER BY for a stable answer, or use RANK or
DENSE_RANK when tied rows should share a place.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Not quite. That is GROUP BY. A window function never removes rows; it adds a value to each existing one.Try another answer.
Right. The partition is order 1231, so the sum covers both its lines (93.56 + 294.30) and is written on both rows.
Not quite. 93.56 would be the sum if each row were its own partition. PARTITION BY order_id puts the lines of one order together.Try another answer.
Not quite. That would need no PARTITION BY at all. With PARTITION BY order_id the sum restarts for each order.Try another answer.
Lesson done: it is ticked on the map.