Window Functions

GROUP BY, from Joins, CTEs & Recursion, collapses matching rows into one row per group — you lose the individual rows in exchange for the aggregate. A window function computes the same kinds of aggregates but keeps every row: a running total next to each transaction, a rank next to each score, the previous row's value next to the current one.

The anatomy of OVER

SELECT
  user_id,
  event_time,
  amount,
  sum(amount) OVER (
      PARTITION BY user_id
      ORDER BY event_time
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM payments;

• PARTITION BY groups rows the same way GROUP BY would — but every row in the group survives in the output, not just the aggregate.
• ORDER BY inside OVER (...) defines the order the window function walks rows in within each partition. It has nothing to do with the final result set's order — you still need an outer ORDER BY for that.
• The frame clause, ROWS BETWEEN ... AND ..., controls exactly which rows around the current one are included in the calculation.

Ranking functions

SELECT
  user_id,
  total_spend,
  row_number() OVER (ORDER BY total_spend DESC) AS row_num,
  rank()       OVER (ORDER BY total_spend DESC) AS spend_rank,
  dense_rank() OVER (ORDER BY total_spend DESC) AS dense_spend_rank
FROM user_totals;

The three differ only in how they treat ties. Given spend values 100, 100, 90:

• row_number() gives 1, 2, 3 — always unique, breaks ties arbitrarily (by whatever order is stable for equal keys).
• rank() gives 1, 1, 3 — ties share a rank, and the next rank skips ahead by the number of tied rows.
• dense_rank() gives 1, 1, 2 — ties share a rank, but the next rank is always just one higher, with no gap.

Other common window functions: lag(col, n) / lead(col, n) return the value of a column n rows before/after the current one in the ordered partition — the standard tool for period-over-period comparisons (this month vs. last month) without a self-join.

Frames and running aggregates

The frame clause is what makes running totals and moving averages possible. Omitting it defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when an ORDER BY is present — which is subtly different from ROWS, so it's worth being explicit:

SELECT
  day,
  revenue,
  sum(revenue) OVER (
      ORDER BY day
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  avg(revenue) OVER (
      ORDER BY day
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3d
FROM daily_revenue
ORDER BY day;

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a physical window: the current row plus the two immediately before it, always three rows wide regardless of ties in ORDER BY. RANGE instead groups by value — every row with an equal ORDER BY value is treated as part of the same peer group and included together, which matters when the ordering column has duplicates.

Window function vs. GROUP BY: same math, different shape

SELECT department_id, avg(salary) AS avg_salary
FROM employees
GROUP BY department_id;

SELECT
  employee_id,
  department_id,
  salary,
  avg(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,
  salary - avg(salary) OVER (PARTITION BY department_id) AS diff_from_avg
FROM employees;

Both compute the same per-department average. GROUP BY throws away the individual employee rows to get there; the window function keeps every employee row and attaches the department's average to each one — which is exactly what you need for something like "how far above or below their department's average does each employee sit," a calculation that's impossible to express with GROUP BY alone since it needs both the individual row and the aggregate at the same time.

Next: turning a query like this into something reusable, in Views, Functions & Triggers.