Window Functions

Window functions perform calculations across a set of rows related to the current row without collapsing them into a single result. Unlike GROUP BY, every input row stays in the output — the function just adds a computed column based on the surrounding "window".

PostgreSQL has full SQL-standard support including ranking, lag/lead, and frame clauses (ROWS / RANGE / GROUPS).

We will use this employees table for examples:

OVER() — The Window Definition

Every window function is followed by an OVER (...) clause that defines the window:

function_name() OVER (
    PARTITION BY col1[, col2, ...]   -- groups (optional)
    ORDER BY col3[, col4, ...]       -- sort within group (optional)
    ROWS|RANGE BETWEEN ... AND ...   -- frame (optional)
)

• PARTITION BY — splits rows into groups, the function resets per group. Without it, the whole result set is one window.
• ORDER BY — orders rows inside the partition; required by ranking and frame-aware functions.
• Frame clause — only applies to aggregates and FIRST_VALUE/LAST_VALUE/NTH_VALUE.

Ranking Functions

ROW_NUMBER() — Unique sequential numbers

Always 1, 2, 3, … even for ties.

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

RANK() and DENSE_RANK() — Handle ties

SELECT name, salary,
       RANK()       OVER (ORDER BY salary DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM employees;

NTILE(n) — Bucket rows into n groups

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

LAG and LEAD — Look at neighbouring rows

Returns a value from the row N positions before (LAG) or after (LEAD) the current one. Useful for computing differences over time.

-- Each hire ordered by date, with the hire before them
SELECT name, hire_date,
       LAG(name)  OVER (ORDER BY hire_date) AS previous_hire,
       LEAD(name) OVER (ORDER BY hire_date) AS next_hire
FROM employees;

LAG(col, offset, default) lets you set a default instead of NULL:

LAG(salary, 1, 0) OVER (ORDER BY hire_date)

Time-series example — month-over-month change

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS delta,
       ROUND(
         100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
              / LAG(revenue) OVER (ORDER BY month), 2
       ) AS pct_change
FROM monthly_revenue;

FIRST_VALUE, LAST_VALUE, NTH_VALUE

Return a value from a specific position within the window.

SELECT name, dept, salary,
       FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_earner,
       LAST_VALUE(name)  OVER (PARTITION BY dept ORDER BY salary DESC
                               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bottom_earner
FROM employees;

Aggregates as Window Functions

Any aggregate (SUM, AVG, COUNT, MIN, MAX, STRING_AGG, ARRAY_AGG, …) can be turned into a window function by adding OVER.

Running total

SELECT name, hire_date, salary,
       SUM(salary) OVER (ORDER BY hire_date) AS running_payroll
FROM employees;

Department average alongside individual salary

SELECT name, dept, salary,
       ROUND(AVG(salary) OVER (PARTITION BY dept), 0) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY dept) AS diff
FROM employees;

Moving average (last 3 rows)

SELECT month, revenue,
       ROUND(AVG(revenue) OVER (
           ORDER BY month
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS moving_avg_3m
FROM monthly_revenue;

The Frame Clause

The frame defines which rows inside the partition the function sees. Without it, the default depends on whether ORDER BY is present.

Frame syntax:

ROWS  BETWEEN <start> AND <end>   -- physical row offsets
RANGE BETWEEN <start> AND <end>   -- value-based (date, number)
GROUPS BETWEEN <start> AND <end>  -- peer groups (PG 11+)

Bounds:

UNBOUNDED PRECEDING
N PRECEDING
CURRENT ROW
N FOLLOWING
UNBOUNDED FOLLOWING

Examples

-- Cumulative count
COUNT(*) OVER (ORDER BY hire_date
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Sliding 7-day window of revenue (date-aware)
SUM(revenue) OVER (ORDER BY day
                   RANGE BETWEEN INTERVAL '6 days' PRECEDING
                             AND CURRENT ROW)

-- Centred moving average (3 rows on either side)
AVG(value) OVER (ORDER BY ts
                 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)

Named Windows with WINDOW Clause

If you reuse the same window definition multiple times, name it once with the WINDOW clause:

SELECT name, dept, salary,
       RANK()       OVER w AS rk,
       DENSE_RANK() OVER w AS dr,
       AVG(salary)  OVER w AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

Cleaner than repeating (PARTITION BY dept ORDER BY salary DESC) three times.

Filtering Window Function Results

Window functions are calculated after WHERE and GROUP BY but before ORDER BY. To filter on a window result, wrap the query in a subquery or CTE.

-- Top 2 earners per department
WITH ranked AS (
    SELECT name, dept, salary,
           RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk
    FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rk <= 2;

Practical Patterns

1. Detect gaps in sequences

SELECT id,
       id - ROW_NUMBER() OVER (ORDER BY id) AS gap_group
FROM tickets;

Rows with the same gap_group are contiguous; transitions mark a gap.

2. Sessionization — group user events into sessions

SELECT user_id, ts, event,
       SUM(CASE WHEN ts - LAG(ts) OVER w > INTERVAL '30 min' THEN 1 ELSE 0 END)
           OVER w AS session_id
FROM events
WINDOW w AS (PARTITION BY user_id ORDER BY ts);

3. Percent of total

SELECT product, sales,
       ROUND(100.0 * sales / SUM(sales) OVER (), 2) AS pct_of_total
FROM product_sales;

4. Change since first measurement

SELECT day, value,
       value - FIRST_VALUE(value) OVER (ORDER BY day) AS change_since_start
FROM metrics;

Performance Notes

• Window functions can be expensive — they typically require a sort unless an index already orders the data the way PARTITION BY ... ORDER BY needs.
• Reusing a window with WINDOW w AS ... lets the planner share the sort across multiple functions in one pass.
• For "top-N per group" with very wide partitions, a LATERAL join with LIMIT can beat ROW_NUMBER filtering.

Summary

• Window functions add per-row computed values without collapsing rows.
• OVER defines the window: PARTITION BY (groups), ORDER BY (sort), frame (range).
• Ranking: ROW_NUMBER, RANK, DENSE_RANK, NTILE.
• Neighbours: LAG, LEAD, FIRST_VALUE, LAST_VALUE.
• Any aggregate becomes a window function with OVER.
• Filter on window results by wrapping in a subquery or CTE.
• Always set an explicit frame for LAST_VALUE.