SQL Window Functions — OVER, PARTITION BY, and When to Use Them

The problem window functions solve

GROUP BY collapses rows into one summary row per group. That is great for totals but useless when you want the total alongside the original row — for example, showing each order's amount and the running total so far, or each employee's salary and their department average for comparison.

Window functions compute aggregate values without collapsing rows. Every input row stays in the result, and the window function adds a new computed column based on a related set of rows (the "window").

The OVER clause

The OVER () clause is what makes a function a window function instead of a regular aggregate. An empty OVER () means "compute over all rows".

-- Each order with the overall average order value alongside it
SELECT
    id,
    customer_id,
    total_amount,
    AVG(total_amount) OVER ()  AS company_avg,
    total_amount - AVG(total_amount) OVER () AS vs_avg
FROM orders
WHERE created_at >= '2026-01-01';
-- Every row is preserved; no GROUP BY needed

Compare this to the GROUP BY alternative, which would require a self-join or subquery to bring the average back to the row level.

PARTITION BY — separate windows per group

PARTITION BY divides rows into independent windows, like a GROUP BY that does not collapse rows.

-- Each order with its customer's total spend and order rank
SELECT
    id,
    customer_id,
    total_amount,
    created_at,
    SUM(total_amount)  OVER (PARTITION BY customer_id) AS customer_lifetime_value,
    COUNT(*)           OVER (PARTITION BY customer_id) AS customer_order_count,
    AVG(total_amount)  OVER (PARTITION BY customer_id) AS customer_avg_order
FROM orders;

Each customer_id is a separate partition. The SUM for order 1001 (customer 7) counts only customer 7's orders; the SUM for order 1002 (customer 12) counts only customer 12's orders.

ORDER BY inside OVER — running totals and ranks

Adding ORDER BY to the window makes the window function position-aware. Without it, aggregate functions look at all rows in the partition at once. With it, they typically see rows up to and including the current row (the default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

-- Running total of revenue per day (cumulative daily revenue)
SELECT
    DATE(created_at)                                     AS order_date,
    SUM(total_amount)                                    AS daily_revenue,
    SUM(SUM(total_amount)) OVER (ORDER BY DATE(created_at)) AS running_total
FROM orders
WHERE status = 'completed'
GROUP BY DATE(created_at)
ORDER BY order_date;

Note the double SUM: the inner SUM aggregates the GROUP BY, producing one row per day. The outer SUM ... OVER then computes the running total across those daily subtotals.

Aggregate window functions vs GROUP BY

-- GROUP BY: one row per department, lose individual employee rows
SELECT department_id, AVG(salary) AS dept_avg
FROM employees
GROUP BY department_id;

-- Window function: all rows preserved, dept avg in a new column
SELECT
    id,
    name,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department_id) AS vs_dept_avg
FROM employees;

Multiple window functions in one query

You can use multiple OVER clauses with different partitions in the same SELECT:

SELECT
    id,
    customer_id,
    product_category,
    total_amount,
    -- Customer-level metrics
    SUM(total_amount) OVER (PARTITION BY customer_id)          AS cust_total,
    -- Category-level metrics
    AVG(total_amount) OVER (PARTITION BY product_category)     AS cat_avg,
    -- Company-wide rank by amount
    RANK()            OVER (ORDER BY total_amount DESC)        AS company_rank
FROM orders;

Named windows — avoiding repetition

When the same window spec appears multiple times, define it once with WINDOW:

SELECT
    id,
    customer_id,
    total_amount,
    SUM(total_amount)   OVER w AS running_total,
    AVG(total_amount)   OVER w AS running_avg,
    COUNT(*)            OVER w AS running_count
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY created_at)
ORDER BY customer_id, created_at;

Recap

Window functions add computed columns that reference a related set of rows without collapsing the result — the key difference from GROUP BY. PARTITION BY divides rows into independent windows; ORDER BY inside OVER makes the window position-aware (running totals, rankings). Use them when you need both the detail row and an aggregate or rank in the same query — a pattern that would otherwise require a self-join or subquery.

More ways to practice