Exploring SQL Window Functions

24 Jul 2026·Data

Sample playground entry: This note demonstrates how a small technical experiment can be documented.

I wanted to compare a grouped SQL query with a window function.

The main difference is that grouped aggregation reduces the number of rows, while a window function can preserve the original row-level detail.

Sample data

order_id	customer_id	amount
1	A	25
2	A	40
3	B	18
4	A	15
5	B	32

Grouped aggregation

SELECT
    customer_id,
    SUM(amount) AS customer_total
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Result:

customer_id	customer_total
A	80
B	50

The original five rows have been reduced to two rows—one for each customer.

Window function

SELECT
    order_id,
    customer_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
    ) AS customer_total
FROM orders
ORDER BY order_id;

Result:

order_id	customer_id	amount	customer_total
1	A	25	80
2	A	40	80
3	B	18	50
4	A	15	80
5	B	32	50

The calculation is performed for each customer, but every original order remains visible.

Running total

Adding ORDER BY inside the window creates a running calculation:

SELECT
    order_id,
    customer_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
ORDER BY
    customer_id,
    order_id;

Possible result:

order_id	customer_id	amount	running_total
1	A	25	25
2	A	40	65
4	A	15	80
3	B	18	18
5	B	32	50

Ranking example

SELECT
    order_id,
    customer_id,
    amount,
    RANK() OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC
    ) AS amount_rank
FROM orders;

This ranks each customer’s orders from highest to lowest amount.

Previous-row comparison

SELECT
    order_id,
    customer_id,
    amount,
    LAG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_id
    ) AS previous_amount
FROM orders;

LAG() retrieves a value from an earlier row without requiring a self-join.

What I learned

GROUP BY changes the level of detail.
Window functions preserve row-level records.
PARTITION BY defines the calculation group.
ORDER BY controls sequence-dependent calculations.
A window frame specifies which related rows participate.
LAG() and LEAD() are useful for comparisons between rows.

Functions to explore next

ROW_NUMBER()
RANK()
DENSE_RANK()
LAG()
LEAD()
FIRST_VALUE()
LAST_VALUE()
Moving averages