SQL window functions: a margin note on every row
Every original row stays. Each one gets annotated with context from its neighbors.
Every time I teach window functions, I start with a notebook.
A table is just rows
Imagine a notebook. Each line is one order someone placed.
Alice Jan 1 $10
Alice Jan 2 $20
Bob Jan 1 $50
Alice Jan 3 $30
Bob Jan 2 $40
Five lines. Five orders. That is the whole table.
A regular sum destroys the notebook
If I ask "what is the total revenue?", I run SUM(revenue) and get one number: $150.
The notebook is gone. I have an answer, but I can no longer see the orders.
If I ask "total per person" with GROUP BY user_id, I get:
Alice $60
Bob $90
Better, but the notebook is still gone. I cannot see individual orders.
Regular sums collapse rows. You trade detail for a summary.
What if you want both?
What if I want to keep every original line, but next to each line, write a little note?
Alice Jan 1 $10 (Alice's total: $60)
Alice Jan 2 $20 (Alice's total: $60)
Bob Jan 1 $50 (Bob's total: $90)
Alice Jan 3 $30 (Alice's total: $60)
Bob Jan 2 $40 (Bob's total: $90)
Five lines in, five lines out. Every original order is still there. But each line now has a little annotation in the margin.
That is a window function. It is a margin note.
How do you write the margin note?
To write a note next to a row, you have to answer one question: when I am standing on this row, which other rows do I look at?
For Alice's Jan 1 order, what rows do I look at to compute her total? Answer: all of Alice's rows. Not Bob's.
So I need a way to say "look at rows that share the same user_id as me." That is what PARTITION BY user_id does. It is a rule for choosing your neighbors.
SUM(revenue) OVER (PARTITION BY user_id)
Read it out loud: "Sum revenue, looking at rows that share my user_id."
When the database is on Alice's Jan 1 row, it looks around, finds all the Alice rows, sums their revenue, writes $60 in the margin. Then it moves to Alice's Jan 2 row, does the same thing, writes $60 again. And so on.
Sometimes order matters
Now suppose I do not want Alice's total. I want her running total: how much she had spent up to and including this order.
Alice Jan 1 $10 (so far: $10)
Alice Jan 2 $20 (so far: $30)
Alice Jan 3 $30 (so far: $60)
To compute "so far," I need two things: look at Alice's rows only (partition), but only the ones on or before this date (order matters now).
SUM(revenue) OVER (PARTITION BY user_id ORDER BY order_date)
Read it: "Sum revenue, looking at rows that share my user_id, processed in date order, including everything up to me."
Adding ORDER BY automatically means "from the start up to this row." The database walks through Alice's orders in date order, and at each row, the margin note shows the sum so far.
Only the last 7 days
Running total includes everything from the beginning. What if I want only a recent window?
I want, for each row: the sum of Alice's orders where the date is within 6 days behind me, plus today. Not all of Alice's rows. Not even all of Alice's rows up to today. Only Alice's rows in the last 7 days.
That is the third rule: the frame.
SUM(revenue) OVER (
PARTITION BY user_id
ORDER BY order_date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
)
Read it: "Sum revenue, among rows with my user_id, in date order, but only ones whose date is between 6 days before me and me."
Three rules, each doing one job:
- Partition: who counts as "my people"
- Order: in what sequence
- Frame: how far back to look
The whole pattern
Every window function follows the same shape:
SOMETHING() OVER (
PARTITION BY ___ -- who are my neighbors?
ORDER BY ___ -- in what order?
[frame] -- how many of them?
)
You are always answering: "Standing on this row, who do I look at, and what do I do with them?"
That is it. That is the whole intuition. Everything else is variations on these three knobs.