SQL Window Functions Clicked When I Stopped Collapsing Rows

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:

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?

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 customer decides 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:

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:

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:

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:

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:

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.

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:

1. Add another order for Ava. Watch how her customer total changes on every row.
2. Remove the explicit ROWS frame from the running-total query, and use only ORDER BY order_date. Predict what will happen to Ben's totals.
3. Replace ROW_NUMBER() with RANK() and rank by order_date DESC only. 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?