Window Functions

Window functions are the feature that separates intermediate SQL from advanced SQL, and most people discover them too late. Before window functions, common analytical problems required awkward self-joins or correlated subqueries. With them, those same problems become clear and concise.

The key insight is this: a window function computes a value for each row by looking at a set of related rows, but unlike GROUP BY, it does not collapse the rows. The original row stays intact. You get the aggregate value sitting alongside the detail that produced it.

The OVER clause

The OVER clause is what turns an ordinary aggregate function into a window function:

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

The result looks like this:

name    | department  | salary | dept_avg
--------|-------------|--------|----------
Alice   | Engineering | 95000  | 102000
Bob     | Engineering | 105000 | 102000
Carol   | Engineering | 106000 | 102000
Diana   | Marketing   | 72000  | 68000
Evan    | Marketing   | 64000  | 68000

Each row keeps its own salary. Each row also gains a dept_avg that reflects the average for its department. No subquery, no join, no separate query. The PARTITION BY clause tells the function to reset its calculation for each department, just as GROUP BY would, except without collapsing the rows.

If you omit PARTITION BY, the window spans the entire result set:

AVG(salary) OVER ()  -- company-wide average on every row

Ranking functions

Three ranking functions assign ordinal positions within a window:

Function	Behavior on ties
ROW_NUMBER()	Always unique; ties get arbitrary sequential numbers
RANK()	Tied rows share a rank; the next rank skips (1, 1, 3...)
DENSE_RANK()	Tied rows share a rank; no gaps (1, 1, 2...)

SELECT
  name,
  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 rank,
  DENSE_RANK()  OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

The most common use case for ranking is finding the top-N rows per group. Wrap the window function in a subquery or CTE and filter on the rank:

SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn = 1;

This finds the single highest-paid employee in each department, handling ties by arbitrarily choosing one. Use RANK() = 1 instead if you want all employees tied for the top spot.

Running totals

When you add ORDER BY to an aggregate window function, it computes a cumulative value. The function calculates using all rows from the start of the partition up to and including the current row:

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

Each row's running_total is the sum of all orders from the beginning up to that date. This is how you build a cumulative revenue chart or a running balance.

LAG and LEAD

LAG and LEAD let you access the value of a column from a different row without a self-join:

SELECT
  order_date,
  amount,
  LAG(amount)  OVER (ORDER BY order_date) AS prev_amount,
  LEAD(amount) OVER (ORDER BY order_date) AS next_amount,
  amount - LAG(amount) OVER (ORDER BY order_date) AS change_from_prev
FROM orders;

LAG looks one row back (by default). LEAD looks one row forward. Both accept an offset as a second argument and a default value as a third, for cases where no adjacent row exists:

LAG(amount, 1, 0) OVER (ORDER BY order_date)  -- defaults to 0 for the first row

NTILE

NTILE(n) divides rows into n roughly equal buckets and returns the bucket number for each row:

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

Employees in the bottom quarter get quartile 1. The top quarter gets quartile 4. Useful for percentile-based segmentation without needing exact percentile cutoffs.

Frame clauses

By default, a window with ORDER BY uses a frame that spans from the first row of the partition to the current row. You can override this with a frame clause to define a different range:

-- 7-day rolling average (including today and the 6 preceding days)
SELECT
  order_date,
  amount,
  AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7day_avg
FROM orders;

ROWS BETWEEN counts rows. RANGE BETWEEN counts by value. For date-based rolling windows, ROWS BETWEEN is usually what you want.

This family of techniques, partitioning, ranking, cumulative sums, row-offset access, and rolling windows, is what makes window functions one of the most versatile tools in SQL.