PostgreSQL Window Functions

Window functions perform calculations across a set of rows that are related to the current row — similar to aggregate functions, but without collapsing rows into one. Each row in the result keeps its identity, and the window function adds a new computed value alongside the existing columns. This makes window functions ideal for rankings, running totals, moving averages, and comparisons between a row and its peers.

Window Function vs Aggregate Function

With GROUP BY and SUM, employees are collapsed into groups and individual rows disappear. With a window function, the individual employee rows are preserved and the department total is added as an extra column for each row.

-- Aggregate: collapses rows
SELECT department, SUM(salary) FROM employees GROUP BY department;

-- Window: preserves rows
SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;

Syntax of a Window Function

function_name() OVER (
    PARTITION BY column
    ORDER BY column
    ROWS/RANGE frame_clause
)

PARTITION BY: Divides rows into groups (windows). The function runs independently within each partition.
ORDER BY: Defines the order of rows within each partition (required for ranking and cumulative functions).
Frame clause: Optional. Specifies a subset of rows relative to the current row (used for sliding windows).

Ranking Functions

ROW_NUMBER()

Assigns a unique sequential number to each row within a partition. No ties — each row gets a distinct number.

SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

Within each department, employees are ranked by salary, highest first. Row 1 is the highest earner in each department.

RANK()

Like ROW_NUMBER but handles ties: two employees with the same salary get the same rank, and the next rank is skipped.

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

If two people earn 80,000 and are both ranked 2, the next person is ranked 4 (not 3). This is the "Olympic" ranking style.

DENSE_RANK()

Like RANK but does not skip numbers after a tie.

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

Two people ranked 2 are followed by rank 3 (not 4). This is "dense" because the sequence has no gaps.

NTILE(n)

Divides rows into n equal-sized buckets and assigns a bucket number to each row.

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

Employees are divided into four salary quartiles. Quartile 1 contains the top 25% earners.

Aggregate Window Functions

Standard aggregate functions like SUM, AVG, COUNT, MIN, and MAX can all be used as window functions by adding an OVER clause.

Running Total (Cumulative SUM)

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

Each row shows its own amount plus the cumulative total of all previous rows (ordered by date). This is a running balance.

Running Average

SELECT sale_date, amount,
       ROUND(AVG(amount) OVER (ORDER BY sale_date), 2) AS running_avg
FROM sales;

Partition Total with Row Detail

SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department) AS dept_total,
       ROUND(salary * 100.0 /
             SUM(salary) OVER (PARTITION BY department), 2) AS pct_of_dept
FROM employees;

Each employee row shows their salary, their department's total salary, and their percentage contribution to the department total — all without collapsing rows.

Value Functions

LAG() and LEAD()

LAG accesses a value from a previous row; LEAD accesses a value from a following row — both without self-joining the table.

SELECT sale_date, amount,
       LAG(amount) OVER (ORDER BY sale_date) AS previous_day_sales,
       amount - LAG(amount) OVER (ORDER BY sale_date) AS day_over_day_change
FROM sales;

This compares each day's sales against the previous day's.

SELECT name, salary,
       LEAD(salary) OVER (ORDER BY salary DESC) AS next_lower_salary
FROM employees;

FIRST_VALUE() and LAST_VALUE()

SELECT name, department, salary,
       FIRST_VALUE(name) OVER (
           PARTITION BY department ORDER BY salary DESC
       ) AS top_earner_in_dept
FROM employees;

Each row shows who the highest earner in its department is.

Frame Clauses (Sliding Windows)

A frame clause limits the rows the window function looks at, relative to the current row. This is used for moving averages and sliding totals.

-- 3-row moving average (current row + 1 before + 1 after)
SELECT sale_date, amount,
       ROUND(AVG(amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
       ), 2) AS moving_avg
FROM sales;

-- Cumulative sum from start to current row
SELECT sale_date, amount,
       SUM(amount) OVER (
           ORDER BY sale_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_total
FROM sales;

Named Windows

When the same window specification is used multiple times, a named window avoids repetition:

SELECT name, department, salary,
       RANK() OVER dept_window AS dept_rank,
       SUM(salary) OVER dept_window AS dept_total
FROM employees
WINDOW dept_window AS (PARTITION BY department ORDER BY salary DESC);

Example: Complete Window Function Analysis

CREATE TABLE monthly_sales (
    month DATE,
    salesperson VARCHAR(50),
    revenue NUMERIC(10,2)
);

INSERT INTO monthly_sales VALUES
    ('2024-01-01', 'Alice', 12000),
    ('2024-01-01', 'Bob',   9500),
    ('2024-02-01', 'Alice', 15000),
    ('2024-02-01', 'Bob',   11000),
    ('2024-03-01', 'Alice', 13500),
    ('2024-03-01', 'Bob',   14000);

SELECT
    month,
    salesperson,
    revenue,
    RANK() OVER (PARTITION BY month ORDER BY revenue DESC) AS monthly_rank,
    SUM(revenue) OVER (PARTITION BY salesperson ORDER BY month) AS running_total,
    LAG(revenue) OVER (PARTITION BY salesperson ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (PARTITION BY salesperson ORDER BY month) AS growth
FROM monthly_sales
ORDER BY salesperson, month;

Key Points

Window functions calculate values across related rows without collapsing them like GROUP BY does.
Every window function uses an OVER clause; PARTITION BY divides rows into groups; ORDER BY sorts within each group.
ROW_NUMBER assigns unique ranks; RANK skips numbers after ties; DENSE_RANK does not skip.
SUM, AVG, and other aggregates become window functions when used with OVER.
LAG and LEAD access values from previous or next rows without a self-join.
Frame clauses (ROWS BETWEEN) define a sliding range of rows for moving calculations.
Named windows (WINDOW clause) reduce repetition when the same specification is used multiple times.

Previous lessons

Back to courses

Next lessons