Window Functions: The Power User Tool

Aggregation Without Collapsing Rows

Standard GROUP BY aggregation is destructive — it collapses many rows into one. If you group orders by region, you lose the individual order details. Sometimes you need both: the aggregate and the individual rows.

Window functions compute an aggregate across a defined set of rows (the "window") while keeping every individual row intact. They use the OVER() clause to define the window.

-- Show each order AND the running total up to that point
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Result:

order_date  | amount | running_total
------------|--------|---------------
2024-01-01  |    10  |    10
2024-01-02  |    20  |    30
2024-01-03  |     5  |    35

Every row is preserved. The running total accumulates. This is impossible with GROUP BY.

Defining the Window: PARTITION BY and ORDER BY

The OVER() clause accepts two optional sub-clauses:

• PARTITION BY — Divides rows into independent groups (like GROUP BY, but without collapsing). The window function resets for each partition.
• ORDER BY — Defines the order within each partition, which determines how running calculations (like running totals or rankings) accumulate.

-- Running total PER customer (resets for each new customer)
SELECT
    user_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS customer_running_total
FROM orders;

Ranking Functions

Window functions also include ranking functions — among the most common in analytics and interview questions:

SELECT
    user_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,
    RANK()       OVER (PARTITION BY user_id ORDER BY amount DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS dense_rnk
FROM orders;

A classic interview problem: "Find the top 3 orders per customer." The solution uses ROW_NUMBER() or RANK() inside a CTE, then filters where rn <= 3.