Window Functions

Aggregate without losing the rows#

GROUP BY collapses rows. A window function calculates across a set of related rows but keeps every row in the output.

SELECT name, category, price,
       AVG(price) OVER (PARTITION BY category) AS category_avg
FROM products;

Each product keeps its own row and also shows its category's average next to it. Doing that with plain SQL means a self-join or a correlated subquery.

PARTITION BY category ▶ split rows into windows
ORDER BY price        ▶ order rows within each window
function              ▶ computed per row, over its window

Ranking#

SELECT name, category, price,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn,
       RANK()       OVER (PARTITION BY category ORDER BY price DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS drnk
FROM products;

With prices 100, 100, 90 the three functions behave differently:

ROW_NUMBER is always unique. RANK ties then skips. DENSE_RANK ties without skipping.

Top N per group#

The classic use case:

SELECT * FROM (
  SELECT name, category, price,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
) t
WHERE rn <= 2;

You must wrap it, because window functions can't appear in WHERE. They're evaluated after WHERE and GROUP BY.

LAG and LEAD#

SELECT id, customer_id, ordered_at,
       LAG(ordered_at)  OVER (PARTITION BY customer_id ORDER BY ordered_at) AS prev_order,
       DATEDIFF(ordered_at,
         LAG(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at)) AS days_since_prev
FROM orders;

LAG looks at the previous row in the window, LEAD at the next. The first row has no previous one, so you get NULL. You can give a default: LAG(x, 1, 0).

Running totals and frames#

SELECT ordered_at,
       SUM(total) OVER (ORDER BY ordered_at
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM order_totals;

Careful: if you write SUM(...) OVER (ORDER BY x) without a frame, the default is RANGE, which treats rows tied on x as one group, giving the same running value for ties. State ROWS explicitly when you want row by row.

Common mistakes#

• Using a window function in WHERE.
• Choosing RANK when you wanted exactly N rows per group (use ROW_NUMBER).
• Forgetting PARTITION BY and ranking the entire table.
• Not giving a deterministic ORDER BY, so ties come out in random order.

Practice#

For each customer, rank their orders by date and show only the most recent one. Then compute each order's total (qty * unit_price), a running total across all orders by date, and the difference from the previous order's total using LAG.