Window Functions
8 methodsFunctions that operate over a 'window' of rows defined by an OVER clause.
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)Assign a unique sequential integer to each row within a partition.
Returns
bigint
Example
sql
SELECT
name,
department,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;RANK() OVER (PARTITION BY ... ORDER BY ...)Assign a rank with gaps. Ties get the same rank; next rank skips.
Returns
bigint
Example
sql
-- Tied rows get the same rank; next rank skips
-- 1, 2, 2, 4
SELECT
name,
score,
RANK() OVER (ORDER BY score DESC) AS rank
FROM players;DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...)Assign a rank without gaps. Ties get the same rank; next rank is consecutive.
Returns
bigint
Example
sql
-- Tied rows get the same rank; next rank is consecutive
-- 1, 2, 2, 3
SELECT
name,
score,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM players;LAG(expr, offset, default) OVER (PARTITION BY ... ORDER BY ...)Return the value of expr from a row that is offset rows before the current row.
Parameters
| Name | Type | Description |
|---|---|---|
| expr | any | Column or expression. |
| offset | int | Rows to look back (default 1). |
| default | any | Value when out of partition (default NULL). |
Returns
any
Example
sql
SELECT
day,
sales,
LAG(sales, 1, 0) OVER (ORDER BY day) AS prev_sales,
sales - LAG(sales, 1, 0) OVER (ORDER BY day) AS diff
FROM daily_sales;LEAD(expr, offset, default) OVER (PARTITION BY ... ORDER BY ...)Return the value of expr from a row that is offset rows after the current row.
Parameters
| Name | Type | Description |
|---|---|---|
| expr | any | Column or expression. |
| offset | int | Rows to look ahead (default 1). |
| default | any | Value when out of partition (default NULL). |
Returns
any
Example
sql
SELECT
day,
sales,
LEAD(sales, 1) OVER (ORDER BY day) AS next_sales
FROM daily_sales;NTILE(n) OVER (PARTITION BY ... ORDER BY ...)Distribute rows into n approximately equal groups, returning the group number (1..n).
Parameters
| Name | Type | Description |
|---|---|---|
| n | int | Number of buckets. |
Returns
int
Example
sql
-- Split employees into 4 salary quartiles
SELECT
name,
salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;SUM(expr) OVER (PARTITION BY ... ORDER BY ...)Running or partitioned sum. Without ORDER BY, returns the partition total per row.
Parameters
| Name | Type | Description |
|---|---|---|
| expr | numeric | Column or expression. |
Returns
numeric
Example
sql
-- Running total per department, ordered by date
SELECT
date,
department,
amount,
SUM(amount) OVER (
PARTITION BY department
ORDER BY date
) AS running_total
FROM transactions;
-- Partition total (no ORDER BY):
SELECT
department,
amount,
SUM(amount) OVER (PARTITION BY department) AS dept_total
FROM transactions;AVG(expr) OVER (PARTITION BY ... ORDER BY ...)Running or partitioned average. With ORDER BY and a frame, computes a moving average.
Parameters
| Name | Type | Description |
|---|---|---|
| expr | numeric | Column or expression. |
Returns
numeric
Example
sql
-- 3-day moving average
SELECT
day,
sales,
AVG(sales) OVER (
ORDER BY day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg
FROM daily_sales;