I needed a simple SQL report: every order, with the customer's total spending in the next column.
My first instinct was GROUP BY.
It gave me the right totals but removed every individual order.
I could calculate the totals separately and join them back to the orders table. That works, but it felt like a lot of work just to put a number next to each row.
Then I came across this:
SUM(amount) OVER (PARTITION BY customer)
Same totals. All the original orders were still there.
That's the idea that made SQL window functions click for me:
GROUP BY combines rows. Window functions calculate across rows while keeping the individual rows.
Let's see how that works, and where it gets interesting.
Step 1: Five orders, two customers
Here's a small dataset you can run yourself:
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer TEXT,
order_date DATE,
amount INTEGER
);
INSERT INTO orders VALUES
(101, 'Ava', '2026-10-01', 90),
(102, 'Ava', '2026-10-03', 40),
(103, 'Ben', '2026-10-01', 75),
(104, 'Ben', '2026-10-04', 60),
(105, 'Ben', '2026-10-04', 15);
Ava spent $130 across two orders. Ben spent $150 across three.
Notice that Ben placed two orders on October 4. That detail matters later.
Step 2: GROUP BY gets the totals but loses the orders
Let's calculate total spending per customer.
SELECT
customer,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer
ORDER BY customer;
Result:
| customer | total_spent |
|---|---|
| Ava | 130 |
| Ben | 150 |
Nothing wrong with that query.
But five rows went in, and only two came out.
The individual order IDs, dates, and amounts aren't available in the result anymore.
That's what GROUP BY does. It changes the grain of the result, meaning what one row represents.
Before grouping, one row represented one order.
After grouping, one row represents one customer.
That's useful when we only want totals.
But what if we want this instead?
| order_id | customer | amount | customer_total |
|---|---|---|---|
| 101 | Ava | 90 | 130 |
| 102 | Ava | 40 | 130 |
| 103 | Ben | 75 | 150 |
| 104 | Ben | 60 | 150 |
| 105 | Ben | 15 | 150 |
Five rows, with the customer total beside each order.
This is where window functions help.
Step 3: OVER() keeps the rows
Take the same SUM() and add OVER():
SELECT
order_id,
customer,
amount,
SUM(amount) OVER (
PARTITION BY customer
) AS customer_total
FROM orders
ORDER BY order_id;
We get exactly the five rows shown above.
The totals haven't changed.
Ava still spent 130. Ben still spent 150.
But we haven't collapsed their orders.
Here's how to read the important part:
SUM(amount) OVER (
PARTITION BY customer
)
-
SUM(amount)calculates the total. -
OVER()tells SQL to perform a window calculation. -
PARTITION BY customerdecides which rows belong to each customer's calculation.
For order 104, SQL looks at Ben's three orders:
75 + 60 + 15 = 150
It attaches that result to order 104 without removing the row.
And it does the same for Ben's other orders.
That's the distinction I wish I'd understood earlier.
GROUP BY customer says, "Give me one result per customer."
PARTITION BY customer inside a window says, "For each order, look at the other orders belonging to the same customer and calculate something."
The rows stay separate.
Step 4: Calculate each order's percentage of the total
Now that we have the customer total beside every order, we can calculate something useful.
What percentage of each customer's spending came from each order?
SELECT
order_id,
customer,
amount,
ROUND(
100.0 * amount /
SUM(amount) OVER (PARTITION BY customer),
1
) AS pct_of_customer
FROM orders
ORDER BY order_id;
Result:
| order_id | customer | amount | pct_of_customer |
|---|---|---|---|
| 101 | Ava | 90 | 69.2 |
| 102 | Ava | 40 | 30.8 |
| 103 | Ben | 75 | 50.0 |
| 104 | Ben | 60 | 40.0 |
| 105 | Ben | 15 | 10.0 |
We get each order's contribution without writing a separate aggregate query and joining it back.
One small detail matters here:
100.0 * amount
Why 100.0 instead of 100?
Because integer division behaves differently across SQL databases.
In SQLite and PostgreSQL, dividing one integer by another can discard the decimal portion.
For Ava, using integer arithmetic would produce 69 and 30 instead of 69.2 and 30.8.
DuckDB handles this division differently, which makes the issue easy to miss when moving SQL between engines.
Using 100.0 makes our intention clear: we want a fractional result.
Step 5: The running-total trap
This is where things get more interesting.
Suppose we want a running total of each customer's spending.
For Ava, that should be:
- First order: 90
- Second order: 130
For Ben:
- First order: 75
- Second order: 135
- Third order: 150
Let's try adding ORDER BY inside the window.
SELECT
order_id,
customer,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer
ORDER BY order_date
) AS running_total
FROM orders
ORDER BY customer, order_date, order_id;
Result:
| order_id | customer | amount | running_total |
|---|---|---|---|
| 101 | Ava | 90 | 90 |
| 102 | Ava | 40 | 130 |
| 103 | Ben | 75 | 75 |
| 104 | Ben | 60 | 150 |
| 105 | Ben | 15 | 150 |
Wait.
Ben's running total jumps from 75 straight to 150.
What happened to 135?
The problem is that Ben has two orders on the same date.
In SQLite, DuckDB, and PostgreSQL, an aggregate window with ORDER BY uses a default frame equivalent to:
RANGE BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
With RANGE, rows sharing the same ordering value are treated as peers.
Both of Ben's October 4 orders have the same order_date.
So each row's window includes both October 4 orders, giving both a running total of 150.
SQL isn't miscalculating.
It's following the window frame we implicitly asked for.
Fix it with ROWS and a tie-breaker
If we want a running total that advances one order at a time, we can write:
SELECT
order_id,
customer,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer
ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS running_total
FROM orders
ORDER BY customer, order_date, order_id;
Now we get:
| order_id | customer | amount | running_total |
|---|---|---|---|
| 101 | Ava | 90 | 90 |
| 102 | Ava | 40 | 130 |
| 103 | Ben | 75 | 75 |
| 104 | Ben | 60 | 135 |
| 105 | Ben | 15 | 150 |
Two things changed.
First, ORDER BY order_date, order_id breaks ties.
If two orders happen on the same date, order_id gives us a consistent ordering.
Second, ROWS makes the frame operate on individual rows.
The calculation starts at the first row in each customer's partition and includes rows up to the current one.
That's the behavior we wanted.
There's another distinction worth remembering.
The ORDER BY inside OVER() controls the window calculation.
The final ORDER BY controls how query results are displayed.
They're separate things.
Step 6: Find each customer's latest order
Another common request:
"Show me the most recent order for every customer."
We can use ROW_NUMBER() for this.
SELECT
order_id,
customer,
order_date,
ROW_NUMBER() OVER (
PARTITION BY customer
ORDER BY order_date DESC, order_id DESC
) AS rn
FROM orders;
ROW_NUMBER() gives each order a position within its customer group.
The newest order gets 1.
The next gets 2.
And so on.
So we should be able to filter for rn = 1, right?
Let's try:
SELECT
order_id,
customer,
ROW_NUMBER() OVER (
PARTITION BY customer
ORDER BY order_date DESC, order_id DESC
) AS rn
FROM orders
WHERE rn = 1;
That fails.
SQLite reports:
misuse of aliased window function rn
DuckDB reports:
WHERE clause cannot contain window functions!
The reason is SQL's logical processing order.
WHERE filters rows before window-function results are available.
So we can't filter on the window result directly in the same query block.
Use a CTE instead
Calculate the row numbers first, then filter:
WITH ranked AS (
SELECT
order_id,
customer,
order_date,
amount,
ROW_NUMBER() OVER (
PARTITION BY customer
ORDER BY order_date DESC, order_id DESC
) AS rn
FROM orders
)
SELECT
order_id,
customer,
order_date,
amount
FROM ranked
WHERE rn = 1
ORDER BY customer;
Result:
| order_id | customer | order_date | amount |
|---|---|---|---|
| 102 | Ava | 2026-10-03 | 40 |
| 105 | Ben | 2026-10-04 | 15 |
Exactly one latest order per customer.
Again, notice the tie-breaker.
Ben has two orders on October 4.
Using order_id DESC makes order 105 the winner.
Without a tie-breaker, the database isn't required to choose the same row every time.
A shortcut: QUALIFY
Some databases, including DuckDB, BigQuery, Snowflake, and Databricks SQL, support QUALIFY.
It lets us filter on window functions without a CTE.
SELECT
order_id,
customer,
order_date,
amount
FROM orders
QUALIFY ROW_NUMBER() OVER (
PARTITION BY customer
ORDER BY order_date DESC, order_id DESC
) = 1;
Much shorter.
But QUALIFY isn't supported everywhere. SQLite, PostgreSQL, and MySQL don't support this syntax.
The CTE approach is more portable.
Step 7: Compare an order with the previous one
One more window function worth knowing is LAG().
It lets you access a value from the previous row in a window.
For example, let's calculate how much each order amount changed compared with the customer's previous order.
SELECT
order_id,
customer,
amount,
amount - LAG(amount) OVER (
PARTITION BY customer
ORDER BY order_date, order_id
) AS change_from_previous
FROM orders
ORDER BY customer, order_date, order_id;
Result:
| order_id | customer | amount | change_from_previous |
|---|---|---|---|
| 101 | Ava | 90 | NULL |
| 102 | Ava | 40 | -50 |
| 103 | Ben | 75 | NULL |
| 104 | Ben | 60 | -15 |
| 105 | Ben | 15 | -45 |
Ava's second order was $50 smaller than her first.
Ben's second order was $15 smaller than his first.
The first order for each customer gets NULL because there isn't a previous order to compare it with.
That's expected.
LEAD() does something similar, but looks at a following row.
No self-join needed.
So when should you use GROUP BY or a window function?
Here's the distinction I keep coming back to.
| What you need | Usually use |
|---|---|
| One row per customer with total spending | GROUP BY |
| Every order with its customer total | SUM() OVER (PARTITION BY ...) |
| A running total | SUM() OVER (... ORDER BY ... ROWS ...) |
| The latest order per customer |
ROW_NUMBER() + CTE or QUALIFY
|
| Compare each order with the previous one | LAG() |
| Compare each order with the next one | LEAD() |
Window functions aren't replacements for GROUP BY.
And they're not automatically faster than calculating aggregates and joining them back.
They solve a different problem.
Use GROUP BY when you want to summarize rows. Use window functions when you need calculations across related rows while keeping the details.
The thing that made window functions click
I used to think PARTITION BY was just a fancier version of GROUP BY.
It isn't.
GROUP BY decides what the rows in your aggregated result represent.
PARTITION BY defines which rows a window calculation looks at.
Once that clicked, running totals, rankings, and latest-record queries stopped feeling like patterns I had to memorize.
They're different ways of calculating something across a window of related rows.
Try it yourself
Take the five orders from this post and experiment:
- Add another order for Ava. Watch how her customer total changes on every row.
- Remove the explicit
ROWSframe from the running-total query, and use onlyORDER BY order_date. Predict what will happen to Ben's totals. - Replace
ROW_NUMBER()withRANK()and rank byorder_date DESConly. Ben's two October 4 orders now share rank 1.
I'm building Runtime, a free hands-on platform for learning SQL and Python by writing and running queries directly in your browser.
Try the examples yourself, change the data, and see how each window behaves.
Which SQL window function took you the longest to understand?
Top comments (1)
With your ROWS frame and tie-breaker, an October 4 filter inside the window query gives Ben running totals of 60 and 75; applying it outside a CTE that calculates all orders gives 135 and 150. Both queries return the same two orders, but answer different questions: spending in the selected period versus lifetime spending on the displayed dates. That would make a useful extra exercise alongside the rn = 1 example.