Glossary

SQL Window Functions

Window functions compute a value for each row relative to a related set of rows (the "window"), without collapsing them into one. They enable rankings, running totals, and comparisons without subqueries.

Syntax

SELECT
  name,
  salary,
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank,
  SUM(salary) OVER (PARTITION BY dept) AS dept_total,
  AVG(salary) OVER () AS company_avg
FROM employees;

Common functions

ROW_NUMBER() — sequential number within the window
RANK() / DENSE_RANK() — rank (with gaps / without gaps)
LAG(col, n) / LEAD(col, n) — value from the previous/next row
SUM / AVG / MAX OVER (...) — aggregates over the window without GROUP BY

Vs GROUP BY

GROUP BY collapses rows into one. A window function keeps all rows and adds a computed value to each.