SQL window functions

Window functions compute a value for each row using a set of related rows — the window — without collapsing those rows together the way GROUP BY does. The result sits alongside the original columns. You get both the per-row values and the aggregate in the same SELECT.

I’m a data engineer, not a data analyst, so I don’t write these every day. The syntax never fully sticks, and I end up back here — or in the PostgreSQL docs — each time. This page is that lookup, written once so I stop re-reading the same three tutorials.

Anatomy

function_name(expression)
  OVER (
    [PARTITION BY col, ...]
    [ORDER BY col [ASC|DESC], ...]
    [ROWS BETWEEN start AND end]
  )

PARTITION BY — splits rows into independent groups; the function resets for each group. Omitting it treats the entire result set as one partition.
ORDER BY — controls the order within each partition. Required for ranking and offset functions; optional for pure aggregates.
ROWS BETWEEN — the frame clause; narrows which rows within the partition are included. See Frame clause.

Where they're allowed

Window functions are evaluated after WHERE, GROUP BY, and HAVING, so they can only appear in SELECT and ORDER BY. Using one in a WHERE or HAVING clause is a syntax error.

The reason is execution order: SQL runs FROM → WHERE → SELECT → ORDER BY. The window function lives in SELECT, so when WHERE is evaluated the window result doesn’t exist yet — the database errors out.

-- wrong: WHERE runs before SELECT, so "dept_rank" doesn't exist yet
SELECT
  department,
  employee_id,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employee
WHERE dept_rank = 1;  -- error

To filter on a window function result, wrap the query in a CTE first. The CTE materialises fully before the outer query runs, so dept_rank is a real column by the time WHERE sees it:

WITH ranked AS (
  SELECT
    department,
    employee_id,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
  FROM employee
)
SELECT * FROM ranked WHERE dept_rank = 1;

Aggregate functions

Standard aggregates (SUM, AVG, COUNT, MIN, MAX) work as window functions when combined with OVER.

Average per group, kept as a column:

SELECT
  customer_id,
  unit_price,
  AVG(unit_price) OVER (PARTITION BY customer_id) AS avg_price
FROM orders
JOIN order_items USING (order_id);

Each row keeps its own unit_price and also shows the average for that customer — no GROUP BY, no join back.

Multiple partitions:

SELECT
  customer_id,
  employee_id,
  AVG(unit_price) OVER (PARTITION BY customer_id, employee_id) AS avg_price
FROM orders
JOIN order_items USING (order_id);

Running total:

ORDER BY without a frame clause defaults to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which gives a cumulative sum:

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Running total per partition:

SELECT
  terminal,
  duration_seconds,
  SUM(duration_seconds) OVER (
    PARTITION BY terminal
    ORDER BY start_time
  ) AS running_total
FROM bike_trips;

Running total as a percentage of overall:

SELECT
  film_id,
  length,
  SUM(length) OVER (ORDER BY film_id)          AS running_total,
  SUM(length) OVER ()                           AS overall_total,
  SUM(length) OVER (ORDER BY film_id) * 100.0
    / SUM(length) OVER ()                       AS running_pct
FROM film
ORDER BY film_id;

OVER () with no arguments uses the full result set as a single window.

Ranking functions

All ranking functions require ORDER BY inside OVER. PARTITION BY is optional — omitting it ranks across the full result set.

Function	Behaviour	Example output
`ROW_NUMBER()`	Unique sequential integer, no ties	1, 2, 3, 4, 5
`RANK()`	Tied rows get the same rank; next rank skips	1, 2, 2, 4, 5
`DENSE_RANK()`	Tied rows get the same rank; no gaps	1, 2, 2, 3, 4
`NTILE(n)`	Distributes rows into n roughly equal buckets	1, 1, 2, 2, 3
`PERCENT_RANK()`	Relative rank as a fraction between 0 and 1	0.0, 0.25, …

Rank salary within department:

SELECT
  department,
  employee_id,
  full_name,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employee;

Assign quartiles:

SELECT
  terminal,
  duration_seconds,
  NTILE(4)   OVER (PARTITION BY terminal ORDER BY duration_seconds) AS quartile,
  NTILE(100) OVER (PARTITION BY terminal ORDER BY duration_seconds) AS percentile
FROM bike_trips;

When to use which ranking function: use ROW_NUMBER when you need a unique key (e.g. deduplication, LIMIT 1 per group via a CTE). Use RANK when ties should produce gaps (competition scoring). Use DENSE_RANK when you want ties without gaps (ranking pages in a report). Use NTILE when you want to bucket rows into equal-size groups.

Offset functions: LAG and LEAD

LAG looks back; LEAD looks forward. Both require ORDER BY.

LAG(expression [, offset [, default]])  OVER (...)
LEAD(expression [, offset [, default]]) OVER (...)

offset defaults to 1 (the immediately adjacent row). default is the value returned when no row exists (the first row for LAG, the last for LEAD) — defaults to NULL.

Previous row’s quantity per product:

SELECT
  product_id,
  order_date,
  quantity,
  LAG(quantity) OVER (PARTITION BY product_id ORDER BY order_date) AS prev_quantity
FROM orders
JOIN order_items USING (order_id);

Difference from previous row:

SELECT
  terminal,
  duration_seconds,
  duration_seconds
    - LAG(duration_seconds, 1) OVER (PARTITION BY terminal ORDER BY duration_seconds)
    AS diff_from_prev
FROM bike_trips;

The first row per partition produces NULL for LAG; the last produces NULL for LEAD.

Day-over-day revenue ratio:

WITH daily AS (
  SELECT
    DATE(payment_ts) AS date,
    SUM(amount)      AS revenue
  FROM payments
  GROUP BY DATE(payment_ts)
)
SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date)                       AS prev_day,
  revenue * 1.0 / LAG(revenue, 1) OVER (ORDER BY date)       AS dod_ratio
FROM daily
ORDER BY date;

Time to next station:

SELECT
  train_id,
  station,
  time                                                           AS arrival,
  LEAD(time) OVER (PARTITION BY train_id ORDER BY time) - time  AS to_next
FROM train_schedule;

Value functions: FIRST_VALUE and LAST_VALUE

FIRST_VALUE and LAST_VALUE return the value from the first or last row of the window frame.

SELECT
  product_id,
  order_date,
  quantity,
  FIRST_VALUE(quantity) OVER (
    PARTITION BY product_id ORDER BY order_date
  ) AS first_quantity_ever
FROM orders
JOIN order_items USING (order_id);

One gotcha with LAST_VALUE: the default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so “last” means the current row, not the last row in the partition. To get the actual last row, specify the frame explicitly:

LAST_VALUE(quantity) OVER (
  PARTITION BY product_id
  ORDER BY order_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Frame clause

The frame clause narrows which rows within the partition are included in the calculation. It only applies when ORDER BY is present.

ROWS BETWEEN <start> AND <end>

Boundary keywords:

Keyword	Meaning
`UNBOUNDED PRECEDING`	First row of the partition
`n PRECEDING`	n rows before the current row
`CURRENT ROW`	The current row
`n FOLLOWING`	n rows after the current row
`UNBOUNDED FOLLOWING`	Last row of the partition

Common frames:

-- Cumulative (default when ORDER BY is present)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- Trailing 3-row moving average (current + 2 before)
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

-- Centred 5-row window
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING

-- Entire partition
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Important: when ORDER BY is present but no frame clause is given, SQL defaults to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This means a SUM becomes a running total rather than a partition total. Be explicit if you want something different.

Cumulative moving average:

SELECT
  customer_id,
  unit_price,
  AVG(unit_price) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_avg
FROM orders
JOIN order_items USING (order_id);

3-row trailing average:

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM sales;

Window aliases

When the same OVER (...) clause appears multiple times, define it once with WINDOW at the end of the query:

SELECT
  terminal,
  duration_seconds,
  NTILE(4)   OVER w AS quartile,
  NTILE(5)   OVER w AS quintile,
  NTILE(100) OVER w AS percentile
FROM bike_trips
WINDOW w AS (PARTITION BY terminal ORDER BY duration_seconds);

WINDOW is supported in PostgreSQL, MySQL 8+, SQLite 3.28+, and BigQuery. It is not supported in SQL Server or older versions of those engines.

Practical patterns

Deduplicate: keep only the most recent record per entity

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
  FROM user_events
)
SELECT * FROM ranked WHERE rn = 1;

Salary as a fraction of department maximum

SELECT
  department,
  employee_id,
  salary,
  salary * 1.0 / MAX(salary) OVER (PARTITION BY department) AS pct_of_max
FROM employee;

Elapsed time from partition start

SELECT
  train_id,
  station,
  time                                                           AS arrival,
  time - MIN(time) OVER (PARTITION BY train_id ORDER BY time)   AS elapsed
FROM train_schedule;

Compare each row to the group average (without a subquery)

SELECT
  title,
  rating,
  replacement_cost,
  AVG(replacement_cost) OVER (PARTITION BY rating) AS avg_for_rating,
  replacement_cost - AVG(replacement_cost) OVER (PARTITION BY rating) AS delta
FROM film;

References

PostgreSQL documentation: Window functions — canonical reference; covers all standard functions plus PostgreSQL-specific behaviour
LearnSQL.com: SQL Window Functions Cheat Sheet — compact visual summary of syntax
LearnSQL.com: SQL Window Function Examples — worked examples with explanations
Mode Analytics: SQL Window Functions — interactive tutorial with a real dataset