Window Functions: Computing Across Rows Without Collapsing Them

A window function performs a calculation across a set of rows related to the current row - and unlike GROUP BY, it keeps every individual row in the result.

The "window" is the set of rows the function looks at for each calculation. You define the window with the OVER clause.

Prior lessons

Window functions build on Aggregate Functions, GROUP BY, and ORDER BY. The sample dataset is defined in the Introduction to SQL lesson.

Syntax

FUNCTION() OVER (
    PARTITION BY column   -- optional: divide rows into groups
    ORDER BY column       -- optional: define row order within the window
)

• PARTITION BY - splits rows into independent windows (like GROUP BY, but without collapsing)
• ORDER BY - orders rows within each window (required for running totals and ranking functions)
• Both are optional - OVER () with nothing applies the function across all rows

GROUP BY vs window functions

	GROUP BY	Window function
Collapses rows?	Yes - one row per group	No - all rows remain
Can mix detail + aggregate?	No	Yes

Examples

Example 1: Per-customer average alongside each order

With GROUP BY you'd lose the individual order rows. With a window function you keep them and add the average as a new column.

SELECT
    order_id,
    customer_id,
    total_amount,
    AVG(total_amount) OVER (PARTITION BY customer_id) AS customer_avg
FROM orders
ORDER BY customer_id, order_id;

Result:

order_id	customer_id	total_amount	customer_avg
1	1	999.00	469.33
2	1	59.98	469.33
6	1	349.00	469.33
3	2	349.00	674.00
7	2	999.00	674.00
4	3	796.00	897.50
9	3	999.00	897.50
5	4	49.90	37.43
10	4	24.95	37.43
8	5	89.97	89.97

Each row retains its detail; customer_avg is computed per partition.

Example 2: Overall average without PARTITION BY

Omitting PARTITION BY applies the function across all rows as a single window.

SELECT
    order_id,
    total_amount,
    AVG(total_amount) OVER () AS overall_avg,
    total_amount - AVG(total_amount) OVER () AS diff_from_avg
FROM orders
ORDER BY order_id;

Result (first few rows):

order_id	total_amount	overall_avg	diff_from_avg
1	999.00	471.58	527.42
2	59.98	471.58	-411.60
3	349.00	471.58	-122.58
...	...	...	...

Example 3: Running total with ORDER BY in OVER

Adding ORDER BY inside OVER makes the window cumulative: each row's window includes all preceding rows (by the specified order) plus itself.

SELECT
    order_id,
    order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date;

Result:

order_id	order_date	total_amount	running_total
1	2024-01-05	999.00	999.00
2	2024-01-10	59.98	1058.98
3	2024-01-15	349.00	1407.98
4	2024-02-01	796.00	2203.98
5	2024-02-10	49.90	2253.88
...	...	...	...

Common Mistake

Window functions cannot be used in WHERE or GROUP BY

The window function result is computed at the SELECT step. You cannot filter on it directly in WHERE. Wrap the query in a CTE or subquery.

-- Fails:
SELECT order_id, RANK() OVER (ORDER BY total_amount DESC) AS rnk
FROM orders
WHERE rnk = 1;

-- Works: wrap in a CTE
WITH ranked AS (
    SELECT order_id, RANK() OVER (ORDER BY total_amount DESC) AS rnk
    FROM orders
)
SELECT * FROM ranked WHERE rnk = 1;

Practice

Write a query that shows each order's order_id, customer_id, total_amount, and the customer's total spend (customer_total) computed as a window function partitioned by customer_id.

Show answer

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