Window Functions

ROW_NUMBER() OVER (ORDER BY column)

SELECT
  ROW_NUMBER() OVER (ORDER BY total_amount DESC) AS rank,
  user_id,
  total_amount
FROM orders;

-- rank | user_id | total_amount
-- 1    | 42      | 999.00
-- 2    | 17      | 875.50
-- 3    | 88      | 849.99

Note ROW_NUMBER assigns a unique sequential number. Ties get arbitrary ordering - two rows with the same total_amount will get different numbers based on physical storage order. Use RANK or DENSE_RANK if you need tie handling.

RANK() OVER (ORDER BY column)
DENSE_RANK() OVER (ORDER BY column)

SELECT
  product_name,
  price,
  RANK() OVER (ORDER BY price DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM products;

-- product_name | price  | rank | dense_rank
-- Laptop       | 999.00 | 1    | 1
-- Tablet       | 499.00 | 2    | 2
-- Phone        | 499.00 | 2    | 2
-- Keyboard     | 49.99  | 4    | 3  <-- rank skips 3, dense_rank doesn't

Note RANK leaves gaps after ties (1, 2, 2, 4). DENSE_RANK does not (1, 2, 2, 3). Choose based on whether you need contiguous numbers or positional accuracy.

window_function() OVER (PARTITION BY column ORDER BY column)

SELECT
  department_id,
  first_name,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) AS dept_rank
FROM employees;

-- department_id | first_name | salary | dept_rank
-- 1             | Alice      | 95000  | 1
-- 1             | Bob        | 82000  | 2
-- 2             | Carol      | 105000 | 1
-- 2             | Dave       | 78000  | 2

Note PARTITION BY divides rows into groups and applies the window function independently within each group. It is like GROUP BY but without collapsing rows. You can partition by multiple columns.

LAG(column, offset, default) OVER (ORDER BY column)
LEAD(column, offset, default) OVER (ORDER BY column)

SELECT
  order_date,
  total_amount,
  LAG(total_amount) OVER (ORDER BY order_date) AS prev_amount,
  total_amount - LAG(total_amount) OVER (ORDER BY order_date) AS change
FROM orders
WHERE user_id = 42;

-- order_date  | total_amount | prev_amount | change
-- 2025-09-01  | 150.00       | NULL        | NULL
-- 2025-10-05  | 230.00       | 150.00      | 80.00
-- 2025-11-12  | 180.00       | 230.00      | -50.00

Note LAG looks at previous rows; LEAD looks at subsequent rows. The optional second argument specifies how many rows to look back/ahead (default 1). The third argument provides a default value instead of NULL for the first/last row.

SUM(column) OVER (ORDER BY column ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

SELECT
  order_date,
  total_amount,
  SUM(total_amount) OVER (ORDER BY order_date) AS running_total,
  AVG(total_amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM orders
WHERE user_id = 42;

-- order_date  | total_amount | running_total | moving_avg_3
-- 2025-09-01  | 150.00       | 150.00        | 150.00
-- 2025-10-05  | 230.00       | 380.00        | 190.00
-- 2025-11-12  | 180.00       | 560.00        | 186.67

Note Without ROWS/RANGE, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all rows with the same ORDER BY value. Use ROWS for exact row-based windows. This distinction matters when ORDER BY has ties.

NTILE(n) OVER (ORDER BY column)

SELECT
  product_name,
  price,
  NTILE(4) OVER (ORDER BY price) AS price_quartile
FROM products;

-- product_name | price  | price_quartile
-- Cable         | 9.99   | 1
-- Mouse         | 29.99  | 1
-- Keyboard      | 49.99  | 2
-- Monitor       | 349.00 | 3
-- Laptop        | 999.00 | 4

Note NTILE divides rows into n approximately equal groups. If the row count is not evenly divisible, earlier groups get one extra row. Useful for percentile bucketing, but the buckets are based on row count, not value distribution.

FIRST_VALUE(column) OVER (ORDER BY column)
LAST_VALUE(column) OVER (ORDER BY column ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

SELECT
  department_id,
  first_name,
  salary,
  FIRST_VALUE(first_name) OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) AS top_earner
FROM employees;

-- Shows each employee alongside their department's top earner

Note LAST_VALUE has a critical gotcha: the default window frame ends at CURRENT ROW, not at the end of the partition. You must add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value.

ROWS BETWEEN start AND end
-- start/end: UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW | n FOLLOWING | UNBOUNDED FOLLOWING

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS weekly_moving_avg,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_total
FROM daily_sales;

-- 7-day moving average alongside cumulative total

Note ROWS counts physical rows. RANGE groups rows with the same ORDER BY value together. For date-based ranges in PostgreSQL, you can use RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW if the ORDER BY column is a date.

SELECT ...
FROM table
WINDOW w AS (PARTITION BY col ORDER BY col);

SELECT
  department_id,
  first_name,
  salary,
  ROW_NUMBER() OVER w AS dept_rank,
  SUM(salary) OVER w AS running_salary,
  AVG(salary) OVER w AS running_avg
FROM employees
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC);

-- Reuses the same window definition for three different functions

Note The WINDOW clause avoids repeating the same OVER specification. It is ANSI SQL and supported in PostgreSQL and MySQL 8+. You can still modify a named window in individual OVER clauses to add frame specs.

PERCENT_RANK() OVER (ORDER BY column)
CUME_DIST() OVER (ORDER BY column)

SELECT
  first_name,
  salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank,
  CUME_DIST() OVER (ORDER BY salary) AS cumulative_dist
FROM employees;

-- first_name | salary | pct_rank | cumulative_dist
-- Dave       | 50000  | 0.00     | 0.25
-- Carol      | 65000  | 0.33     | 0.50
-- Bob        | 82000  | 0.67     | 0.75
-- Alice      | 95000  | 1.00     | 1.00

Note PERCENT_RANK returns (rank - 1) / (total - 1), ranging from 0 to 1. CUME_DIST returns the fraction of rows with values less than or equal to the current row. Both are useful for percentile analysis.