Window Functions

Introduction

GROUP BY squashes many rows into one. But sometimes you want to keep every row and still add a calculation that looks across other rows: "this product's rank by price", "the running total so far", "the difference from the previous order". That is what window functions do.

The shape of a window function

A window function ends with OVER (...). The brackets describe the "window" of rows to look at.

OVER () with nothing inside means "all rows". Every product keeps its own row, and each one also shows the overall average. Compare that with GROUP BY, which would give a single row.

PARTITION BY: a window per group

PARTITION BY splits the window into groups, a bit like GROUP BY but without collapsing the rows.

Ranking rows

Add ORDER BY inside OVER to rank rows.

• ROW_NUMBER gives 1, 2, 3, ... even when values tie.
• RANK gives tied rows the same number and then skips ahead (1, 2, 2, 4).
• DENSE_RANK gives tied rows the same number without skipping (1, 2, 2, 3).

Our prices are all different, so the three columns match. Category sizes do tie, so this version shows the difference:

The top product in each category

A classic question. Rank within each category, then keep rank 1. You cannot filter on a window function directly, so wrap it in a CTE.

Running totals

With SUM(...) OVER (ORDER BY ...) each row adds to everything before it.

LAG and LEAD: look at neighbours

LAG(column) reads the value from the previous row, and LEAD(column) the next one. They are ideal for "change since last time".

The first row has no previous row, so its LAG is NULL.

Try it yourself

For each customer, show each of their orders with a column order_number that numbers their orders 1, 2, 3 in date order. Use ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...).

Summary

• A window function adds a calculation across related rows while keeping every row.
• OVER (PARTITION BY ... ORDER BY ...) sets the groups and the order inside them.
• ROW_NUMBER, RANK and DENSE_RANK rank rows. SUM(...) OVER makes running totals.
• LAG and LEAD read the previous and next rows. Wrap the query in a CTE to filter on the result.