How Window Functions Work

Window functions are one of the most powerful features in SQL — and one of the most misunderstood. Once you truly understand them, a whole class of analytical queries that previously required multiple subqueries or application-level code become clean, readable, single-pass SQL.

This lesson builds the complete mental model. Every other lesson in this section builds on it.

The core problem window functions solve

Suppose you want to show every completed order alongside the customer's total lifetime spend. With what you know so far, you have two options:

Option A — subquery per row (slow, ugly):

SELECT
    o.order_id,
    c.name,
    SUM(oi.quantity * oi.unit_price) AS order_total,
    (
        SELECT SUM(oi2.quantity * oi2.unit_price)
        FROM orders o2
        JOIN order_items oi2 ON oi2.order_id = o2.order_id
        WHERE o2.customer_id = o.customer_id
          AND o2.status = 'completed'
    ) AS lifetime_spend
FROM orders o
JOIN customers   c  ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
WHERE o.status = 'completed'
GROUP BY o.order_id, c.name, c.customer_id;

This works but runs a correlated subquery for every single row — catastrophic on large tables.

Option B — window function (one pass, clean):

SELECT
    o.order_id,
    c.name,
    SUM(oi.quantity * oi.unit_price)                            AS order_total,
    SUM(SUM(oi.quantity * oi.unit_price))
        OVER (PARTITION BY o.customer_id)                       AS lifetime_spend
FROM orders o
JOIN customers   c  ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
WHERE o.status = 'completed'
GROUP BY o.order_id, o.customer_id, c.name;

Same result. One scan. This is what window functions are for.

The anatomy of a window function

function_name(args) OVER (
    PARTITION BY partition_expression
    ORDER BY     sort_expression
    frame_clause
)

Each part is optional:

An empty OVER () means: one window covering the entire result set, no ordering, no frame.

PARTITION BY — the window boundary

PARTITION BY is like GROUP BY for the window — it defines which rows are "visible" to each function call. Unlike GROUP BY, the rows are not collapsed.

-- Each order's total AND the country-level total, side by side
SELECT
    o.order_id,
    c.country,
    SUM(oi.quantity * oi.unit_price)                         AS order_total,
    SUM(SUM(oi.quantity * oi.unit_price))
        OVER (PARTITION BY c.country)                        AS country_total
FROM orders o
JOIN customers   c  ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
WHERE o.status = 'completed'
GROUP BY o.order_id, c.country;

For every order in the US, country_total shows the US total. For every UK order, it shows the UK total. No collapsing — every order row is preserved.

ORDER BY within OVER — changes what gets computed

Adding ORDER BY to OVER() doesn't just sort the output. It fundamentally changes what the window function computes by introducing a default frame of all rows from the partition start up to the current row.