Advanced · Lesson 11 of 14

Window functions

Aggregate without collapsing rows.

OVER changes the frame, not the grain

SUM(x) OVER (PARTITION BY group ORDER BY date) computes a total over a moving window while keeping every detail row. That is impossible with GROUP BY, and it is why window questions decide mid to senior interviews.

Running totals

SUM(total) OVER (ORDER BY day) with the default frame gives a cumulative running total. Add PARTITION BY and the running total resets per group.

Rows versus the whole set

The optional frame clauses such as ROWS BETWEEN specify which neighbors count. Defaults cover most interview questions, so focus on PARTITION BY and ORDER BY first.

Examples

Per group total, detail preserved

Every order row plus the customers lifetime total.

SELECT c.name, o.total_amount, SUM(o.total_amount) OVER (PARTITION BY c.customer_id) AS customer_total FROM customers c JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = 'completed' ORDER BY c.name;

Running total

Cumulative revenue day by day.

SELECT order_date AS day, SUM(total_amount) OVER (ORDER BY order_date) AS running FROM orders WHERE status = 'completed' GROUP BY order_date ORDER BY day;