SQL Window Functions — Examples and Explanations

Window functions are one of SQL's most powerful tools. They let you perform calculations across sets of rows without collapsing results like GROUP BY.

What is a Window Function?

A regular aggregate (SUM, COUNT) with GROUP BY returns one row per group. A window function computes a value for each row using a "window" — a set of related rows.

SELECT name, department, salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Each employee stays in the result but gets their department's average salary.

OVER() Syntax

function() OVER (
  [PARTITION BY column]   -- split into groups
  [ORDER BY column]       -- order within group
  [ROWS/RANGE frame]      -- window boundaries
)

ROW_NUMBER, RANK, DENSE_RANK

ROW_NUMBER() — unique row number within the window:

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

RANK() — rank with gaps for ties (1, 2, 2, 4). DENSE_RANK() — no gaps (1, 2, 2, 3).

LAG and LEAD — Access Adjacent Rows

SELECT date, revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_day,
  revenue - LAG(revenue) OVER (ORDER BY date) AS growth
FROM daily_sales;

Running Total

SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

Moving Average

SELECT date, revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

NTILE — Split into Groups

SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

Practical Tasks

Top 3 earners per department — ROW_NUMBER + PARTITION BY
Month-over-month difference — LAG
Cumulative percentage — SUM OVER / total SUM × 100
Median — PERCENTILE_CONT(0.5) WITHIN GROUP