Window Functions

Window functions perform calculations across a set of table rows that are related to the current row. Unlike regular aggregate functions, window functions do not cause rows to become grouped into a single output row - the rows retain their separate identities.

Syntax

function_name([expression]) OVER (
    [PARTITION BY partition_expression [, ...]]
    [ORDER BY sort_expression [ASC | DESC] [, ...]]
    [frame_clause]
)

• PARTITION BY: Divides rows into groups (partitions) that share the same values
• ORDER BY: Defines the order of rows within each partition
• frame_clause: Defines which rows within the partition to include (see Window Frames)

A window function column without an alias is named as the query wrote it, its OVER clause included: SELECT SUM(price) OVER (PARTITION BY category) gives a column named SUM(price) OVER (PARTITION BY category). The call is written as an aggregate column is named, so a FILTER or the call’s own ORDER BY is not part of the name.

Available Window Functions

Ranking Functions

ROW_NUMBER()

Assigns a unique sequential number to each row within a partition.

SELECT name, dept, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num
FROM employees;

Result:

name   | dept        | salary | row_num
-------+-------------+--------+--------
Diana  | Engineering | 80000  | 1
Frank  | Engineering | 75000  | 2
Charlie| Engineering | 70000  | 3
Bob    | Sales       | 60000  | 4
Eve    | Sales       | 55000  | 5
Alice  | Sales       | 50000  | 6

RANK()

Assigns a rank to each row within a partition. Rows with equal values receive the same rank, with gaps in the sequence.

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;

If two employees have the same salary and are ranked 2, the next rank would be 4 (not 3).

DENSE_RANK()

Similar to RANK(), but without gaps in the ranking sequence.

SELECT name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

If two employees have the same salary and are ranked 2, the next rank would be 3.

NTILE(n)

Distributes rows into a specified number of buckets.

SELECT name, salary,
       NTILE(3) OVER (ORDER BY salary DESC) as tertile
FROM employees;

Divides employees into 3 groups based on salary. n is an INTEGER of 1 or more; any other value, including NULL, is an error.

PERCENT_RANK()

Returns the relative rank of the current row as a percentage (0 to 1).

SELECT name, salary,
       PERCENT_RANK() OVER (ORDER BY salary) as pct_rank
FROM employees;

CUME_DIST()

Returns the cumulative distribution of a value (fraction of rows with values <= current row).

SELECT name, salary,
       CUME_DIST() OVER (ORDER BY salary) as cume_dist
FROM employees;

Value Functions

The expression of LAG, LEAD, FIRST_VALUE, LAST_VALUE and NTH_VALUE may be any expression over the row, such as salary * 12 or a subquery; it is computed for each row of the partition, and the function returns the value it has at the row it picks.

LAG(expression [, offset [, default]])

Accesses a value from a previous row within the partition.

-- Get previous row's salary
SELECT name, salary,
       LAG(salary) OVER (ORDER BY salary) as prev_salary
FROM employees;

-- Get salary from 2 rows back, with default of 0
SELECT name, salary,
       LAG(salary, 2, 0) OVER (ORDER BY salary) as prev2_salary
FROM employees;

The offset is 1 when omitted. It is an INTEGER of 0 or more, read as every INTEGER argument is; any other value, including NULL, is an error. The same holds for LEAD.

LEAD(expression [, offset [, default]])

Accesses a value from a following row within the partition.

-- Get next row's salary
SELECT name, salary,
       LEAD(salary) OVER (ORDER BY salary) as next_salary
FROM employees;

-- Get salary from 2 rows ahead, with default of 0
SELECT name, salary,
       LEAD(salary, 2, 0) OVER (ORDER BY salary) as next2_salary
FROM employees;

FIRST_VALUE(expression)

Returns the first value within the partition.

SELECT name, dept, salary,
       FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) as max_in_dept
FROM employees;

LAST_VALUE(expression)

Returns the last value within the current window frame.

SELECT name, dept, salary,
       LAST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) as current_last
FROM employees;

NTH_VALUE(expression, n)

Returns the nth value within the current window frame, or NULL when the frame holds fewer than n rows. n is an INTEGER of 1 or more; any other value, including NULL, is an error. It is evaluated only for a frame that holds rows.

SELECT name, salary,
       NTH_VALUE(salary, 2) OVER (ORDER BY salary DESC) as second_highest
FROM employees;

Using PARTITION BY

PARTITION BY divides the result set into partitions, and window functions are applied to each partition separately.

-- Row numbers within each department
SELECT name, dept, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) as dept_rank
FROM employees
ORDER BY dept, dept_rank;

Result:

name   | dept        | salary | dept_rank
-------+-------------+--------+----------
Diana  | Engineering | 80000  | 1
Frank  | Engineering | 75000  | 2
Charlie| Engineering | 70000  | 3
Bob    | Sales       | 60000  | 1
Eve    | Sales       | 55000  | 2
Alice  | Sales       | 50000  | 3

Aggregate Functions as Window Functions

Standard aggregate functions can also be used as window functions:

-- Running total
SELECT name, salary,
       SUM(salary) OVER (ORDER BY salary) as running_total
FROM employees
ORDER BY salary;

Result:

name   | salary | running_total
-------+--------+--------------
Alice  | 50000  | 50000
Eve    | 55000  | 105000
Bob    | 60000  | 165000
Charlie| 70000  | 235000
Frank  | 75000  | 310000
Diana  | 80000  | 390000

Common Use Cases

Top N per Group

Find the highest paid employee in each department:

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

Running Totals

Calculate cumulative sales:

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) as cumulative_sales
FROM orders;

Compare to Previous/Next

Calculate month-over-month change:

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) as change
FROM monthly_sales;

Percentile Calculation

Find employees in the top 10% of salaries:

SELECT * FROM (
    SELECT name, salary,
           PERCENT_RANK() OVER (ORDER BY salary DESC) as pct
    FROM employees
) ranked
WHERE pct <= 0.10;

Window Frames

Window frames control exactly which rows within a partition are included in the calculation. There are two frame types: ROWS (physical row offsets) and RANGE (logical value ranges).

Syntax

{ ROWS | RANGE } BETWEEN frame_start AND frame_end
{ ROWS | RANGE } frame_start

Frame Bounds

Bound	Description
UNBOUNDED PRECEDING	Start of the partition
n PRECEDING	n rows/values before the current row
CURRENT ROW	The current row
n FOLLOWING	n rows/values after the current row
UNBOUNDED FOLLOWING	End of the partition

Frame Offsets

The n of n PRECEDING and n FOLLOWING can be a literal, a parameter or a constant expression such as 1 + 1. It is evaluated once for the statement and cannot reference a column.

• For ROWS, n is a number of rows: an INTEGER of 0 or more, read as every INTEGER argument is (2.0 is 2, '2' is 2).
• For RANGE, n is a distance in the units of the ORDER BY: an INTEGER or FLOAT of 0 or more, so 0.5 PRECEDING works. It is measured in the sort direction, so with ORDER BY x DESC, 1 PRECEDING reaches the rows whose value is up to 1 above the current one. A row whose ORDER BY value is NULL or not a number frames to its peers.

Any other value, including NULL and a negative number, is an error. A frame whose start lies after its end holds no rows.

Default Frame

When ORDER BY is specified without an explicit frame, the default is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

When ORDER BY is omitted, the entire partition is used. An explicit frame applies with or without ORDER BY.

Examples

-- Running total (default frame with ORDER BY)
SELECT name, salary,
       SUM(salary) OVER (ORDER BY salary) as running_total
FROM employees;

-- 3-row moving average
SELECT name, salary,
       AVG(salary) OVER (
           ORDER BY salary
           ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
       ) as moving_avg
FROM employees;

-- Cumulative sum across entire partition
SELECT name, dept, salary,
       SUM(salary) OVER (
           PARTITION BY dept
           ORDER BY salary
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) as dept_running_total
FROM employees;

-- Total of current and all following rows
SELECT name, salary,
       SUM(salary) OVER (
           ORDER BY salary
           ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
       ) as remaining_total
FROM employees;

ROWS vs RANGE

• ROWS: Counts physical rows. 1 PRECEDING means exactly one row before.
• RANGE: Uses logical value ranges. 1 PRECEDING means all rows whose ORDER BY value is within 1 of the current row’s value.

-- ROWS: exactly 2 preceding physical rows
SUM(salary) OVER (ORDER BY salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- RANGE: all rows with value within the preceding range
SUM(salary) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Named Windows (WINDOW Clause)

The WINDOW clause defines reusable window specifications to avoid repetition when multiple window functions share the same partitioning and ordering.

Syntax

SELECT
    function1() OVER w,
    function2() OVER w
FROM table_name
WINDOW w AS (PARTITION BY col ORDER BY col2);

Example

-- Without WINDOW clause (repetitive)
SELECT name, dept, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) as rn,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rnk,
       SUM(salary) OVER (PARTITION BY dept ORDER BY salary DESC) as running
FROM employees;

-- With WINDOW clause (cleaner)
SELECT name, dept, salary,
       ROW_NUMBER() OVER w as rn,
       RANK() OVER w as rnk,
       SUM(salary) OVER w as running
FROM employees
WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

Notes

• When ORDER BY is omitted, the entire partition is treated as a single group
• NULL values are handled according to SQL standards
• Window functions can only appear in SELECT and ORDER BY clauses
• Multiple window functions can be used in the same query