Skip to content

SQL window functions

ROW_NUMBER, RANK, and running totals — the window functions that come up most often.

SQL

Ranking

SELECT
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

RANK() leaves gaps after ties; DENSE_RANK() doesn’t; ROW_NUMBER() ignores ties entirely.

Running totals

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Comparing to the previous row

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