SQL: window functions in five examples

October 11, 2026

Window functions feel like a dialect of SQL you learn in a parallel universe, but they come down to one idea: aggregate without collapsing rows. A GROUP BY squashes the table; a window function keeps every row and adds the aggregate beside it.

All examples below work in PostgreSQL; most are identical elsewhere.

1. A total next to every row

SELECT
  employee,
  department,
  salary,
  SUM(salary) OVER () AS payroll_total
FROM salaries;

Every row survives, and each carries the same grand total.

2. Subtotals per group

SELECT
  employee,
  department,
  salary,
  SUM(salary) OVER (PARTITION BY department) AS dept_total,
  ROUND(
    100.0 * salary / SUM(salary) OVER (PARTITION BY department)
  , 1) AS pct_of_dept
FROM salaries;

PARTITION BY is “group by, but without collapsing”. This query shape — a row’s share of its group’s total — replaces most of the self-joins people used to write.

3. Ranking: ROW_NUMBER vs RANK vs DENSE_RANK

SELECT
  employee,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM salaries;

For salaries 100, 100, 90:

function	result
`ROW_NUMBER()`	1, 2, 3 — strict positions, ties broken arbitrarily
`RANK()`	1, 1, 3 — ties share a rank, gaps after
`DENSE_RANK()`	1, 1, 2 — ties share a rank, no gaps

The classic “top N per group” is just this plus a filter:

SELECT * FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS n
  FROM salaries
) t
WHERE n <= 3;

4. Running totals

SELECT
  ordered_at::date AS day,
  amount,
  SUM(amount) OVER (ORDER BY ordered_at) AS running_total
FROM orders;

With ORDER BY inside OVER, the frame grows row by row — a cumulative sum. Add PARTITION BY customer_id and it restarts per customer.

5. Comparing to the previous row

SELECT
  ordered_at::date AS day,
  amount,
  amount - LAG(amount) OVER (ORDER BY ordered_at) AS change_from_prev,
  amount - FIRST_VALUE(amount) OVER (ORDER BY ordered_at) AS change_from_first
FROM orders;

LAG/LEAD peek one row back or forward — day-over-day deltas, the previous status of a ticket, the time between events.

One mental model

Read OVER (PARTITION BY p ORDER BY o) as: for each row, take the rows that share its p, sort them by o, and compute the function over the rows up to and including the current one. Everything else — ROWS BETWEEN, named windows, FILTER — is refinement of that sentence.