Window Functions

the core idea

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;
-- every employee row is kept — dept_avg just repeats the department's average on each row

OVER (...) is what makes a function a window function instead of a regular aggregate. PARTITION BY defines the groups (like GROUP BY, but rows aren't collapsed); without it, the "window" is the entire result set.

ranking functions

SELECT
  name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drnk
FROM employees;

Function	Behavior on ties
`ROW_NUMBER()`	always unique, 1/2/3/4/5 — ties broken arbitrarily by insertion/physical order unless the ORDER BY is itself unique
`RANK()`	ties share a rank, next rank skips: 1/2/2/4/5
`DENSE_RANK()`	ties share a rank, no gap: 1/2/2/3/4

ROW_NUMBER() is also the standard trick for "top N per group" and for deduplication — wrap it in a CTE, filter WHERE rn = 1 (or <= n) in the outer query.

LAG and LEAD

SELECT
  order_date, amount,
  LAG(amount) OVER (ORDER BY order_date) AS prev_amount,
  amount - LAG(amount) OVER (ORDER BY order_date) AS change
FROM orders
ORDER BY order_date;

LAG(col, n) reads a value from n rows before the current one (default n=1); LEAD reads ahead. This is the standard way to compute period-over-period change without a self-join.

running totals and frame clauses

SELECT
  order_date, amount,
  SUM(amount) OVER (ORDER BY order_date
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders
ORDER BY order_date;

The frame clause (ROWS BETWEEN ... AND ...) controls exactly which rows within the partition contribute to each row's calculation. The default frame, when an ORDER BY is present but no explicit frame is given, is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which is what makes a plain SUM(...) OVER (ORDER BY ...) behave like a running total in the first place, not an accident.