Window functions — aggregating without collapsing rows

GROUP BY answers "the total per group," collapsing each group into one row. Window functions answer "the total per group, attached to every original row" — genuinely different output shapes, for a genuinely common class of question GROUP BY can't express.

Advanced

4 min read

The shape GROUP BY structurally can't produce

-- "Show every order, AND how it ranks against other orders from the same customer"
-- GROUP BY customer_id would collapse this down to one row per customer — but the
-- question needs every individual order row to still exist, just with an extra
-- per-customer-computed value attached

GROUP BY (from the aggregation lesson) collapses many rows into one row per group — exactly right for "total revenue per customer," genuinely wrong for "every order, annotated with something computed across its customer's other orders," since that needs every original order row to survive in the output. This is precisely the gap window functions fill: computing an aggregate-like value across a group of rows, without collapsing those rows down to one.

The basic shape: `OVER (PARTITION BY ...)`

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

id | customer_id | amount | customer_total
1  | 1           | 50     | 150      <- this customer's orders sum to 150
2  | 1           | 100    | 150      <- same total, attached to every one of their rows
3  | 2           | 75     | 75

OVER (PARTITION BY customer_id) tells SUM to compute its total across each customer's group of rows — the same grouping GROUP BY would do — but instead of collapsing to one row per group, every original order row survives, each one annotated with its own customer's total. PARTITION BY is doing the same conceptual job as GROUP BY's grouping, just without the collapsing side effect.

`ROW_NUMBER`, `RANK`, and `DENSE_RANK`: numbering rows within a group

SELECT
    id, customer_id, amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank_by_amount
FROM orders;

id | customer_id | amount | rank_by_amount
2  | 1           | 100    | 1     <- customer 1's largest order
1  | 1           | 50     | 2     <- customer 1's second-largest
3  | 2           | 75     | 1     <- customer 2's largest (only one order)

ROW_NUMBER() assigns a unique, sequential number within each partition, in whatever order ORDER BY inside the OVER(...) clause specifies — here, each customer's own orders numbered by amount, largest first. RANK() is similar but gives tied rows the same number and then skips ahead (two rows tied for 1st means the next rank is 3, not 2); DENSE_RANK() also ties equally but doesn't skip (the next rank after a tie is 2). This is the direct, efficient way to answer "the top N orders per customer" — a question that's genuinely awkward to express with plain GROUP BY at all, since "top N within each group" isn't a single aggregate value the way a sum or average is.

The window frame: not always "the whole partition"

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

id | order_date | amount | running_total
1  | 2024-01-01 | 50     | 50
2  | 2024-01-05 | 30     | 80    <- 50 + 30
3  | 2024-01-10 | 20     | 100   <- 50 + 30 + 20

The window doesn't have to be the entire partition — ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW narrows it to "every row up to and including this one, in the specified order," which is exactly what produces a running total rather than a fixed per-group sum. This is genuinely difficult to express any other way in plain SQL — window frames are the mechanism specifically built for "compute this aggregate, but only over a sliding or growing window of rows relative to the current one."

Why this matters: the same query, side by side

-- GROUP BY: one row per customer, orders themselves are gone from the output
SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;
 
-- Window function: every order row survives, each annotated with its customer's total
SELECT id, customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id) FROM orders;

The genuine skill is recognizing which shape a question actually needs: "one summary row per group" is GROUP BY's job; "every original row, with a per-group value attached" or "a running/ranked value relative to nearby rows" is a window function's job. Reaching for GROUP BY when the real need is per-row detail alongside a group-level computation forces an awkward extra JOIN back to the original table just to recover the rows GROUP BY collapsed away — window functions are the direct way to avoid that entirely.

Window functions — aggregating without collapsing rows

The shape GROUP BY structurally can't produce

The basic shape: OVER (PARTITION BY ...)

ROW_NUMBER, RANK, and DENSE_RANK: numbering rows within a group

The window frame: not always "the whole partition"

Why this matters: the same query, side by side

Further reading

The basic shape: `OVER (PARTITION BY ...)`

`ROW_NUMBER`, `RANK`, and `DENSE_RANK`: numbering rows within a group