Window functions

What makes them different

An aggregate with GROUP BY collapses rows. A window function computes across a set of rows while keeping every row.

SELECT order_id, region, total,
       SUM(total) OVER (PARTITION BY region) AS region_total,
       total / SUM(total) OVER (PARTITION BY region) AS pct_of_region
FROM orders;

One query gives you the detail and its share of the group — no self-join, no subquery.

Ranking

ROW_NUMBER() OVER (PARTITION BY region ORDER BY total DESC)
RANK()       OVER (PARTITION BY region ORDER BY total DESC)
DENSE_RANK() OVER (PARTITION BY region ORDER BY total DESC)

On ties: ROW_NUMBER assigns distinct sequential numbers arbitrarily. RANK gives ties the same rank then skips (1,2,2,4). DENSE_RANK gives ties the same rank and does not skip (1,2,2,3).

The difference between these three is asked in almost every SQL interview at analyst level.

Top-N-per-group is the canonical use — and remember window functions cannot go in WHERE, so it needs a wrapper:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY total DESC) AS rn
  FROM orders
) t WHERE rn <= 3;

LAG, LEAD and running totals

LAG(revenue)  OVER (ORDER BY month)   -- previous row
LEAD(revenue) OVER (ORDER BY month)   -- next row
SUM(revenue)  OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AVG(revenue)  OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Month-over-month growth is (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month).

A trap worth knowing: adding ORDER BY inside OVER changes the default frame from the whole partition to RANGE UNBOUNDED PRECEDING AND CURRENT ROW. So SUM(x) OVER (ORDER BY d) is a running total, while SUM(x) OVER () is the grand total. That surprises people.

ROWS vs RANGE

ROWS counts physical rows. RANGE includes all rows with the same ORDER BY value as the current row — so with duplicate dates, RANGE pulls in peers that ROWS would not.

When you want exactly n preceding rows, say ROWS explicitly. Leaving it to the default is a quiet source of off-by-a-few errors in moving averages.