Most developers know SELECT, JOIN, and GROUP BY. Window functions are where the gap between mid and senior starts to show.

What makes window functions different

GROUP BY collapses rows. You lose the individual record — it becomes part of an aggregate.

Window functions don't collapse anything. They compute an aggregate over a set of rows while keeping every row intact. That's the core idea.

-- GROUP BY: one row per department
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Window function: every employee row, plus their department average
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Same average calculation. Completely different result shape. Window functions let you ask "how does this row relate to the group?" — something GROUP BY simply can't do.

The anatomy of a window function

function_name() OVER (
    PARTITION BY col1, col2   -- defines the "group" (optional)
    ORDER BY col3             -- defines row order within the group
    ROWS BETWEEN ...          -- defines the frame (optional)
)

• PARTITION BY — splits rows into independent windows. Omit it and the entire result set is one window.
• ORDER BY — required for ranking and frame-sensitive functions. Changes the meaning of aggregates.
• frame clause — defines which rows are "visible" to the function. Default varies by function.

Ranking functions

ROW_NUMBER()

Assigns a unique sequential number to each row within the partition. No ties — if two rows are equal, the order is arbitrary but distinct.

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

Classic use case: get the top N rows per group without a subquery per group.

-- Top earner per department
SELECT * FROM (
    SELECT
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) ranked
WHERE rn = 1;

RANK() vs DENSE_RANK()

Both handle ties, but differently:

• RANK() — tied rows get the same rank, next rank skips. (1, 2, 2, 4)
• DENSE_RANK() — tied rows get the same rank, next rank does not skip. (1, 2, 2, 3)

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

Use DENSE_RANK() when you want "2nd place" to actually mean second, not "tied for second, third place vacant."

Offset functions: LAG and LEAD

These let you access another row's value from the current row — without a self-join.

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

-- Month-over-month revenue change
SELECT
    month,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS delta
FROM monthly_revenue;

-- Time between consecutive orders per user
SELECT
    user_id,
    order_date,
    LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS next_order,
    LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date) - order_date AS days_until_next
FROM orders;

LAG/LEAD replace a category of self-joins that are painful to read and slow to execute. If you see a query joining a table to itself on id - 1 or date arithmetic, it's almost always a LAG in disguise.

Aggregate window functions

Any aggregate (SUM, AVG, COUNT, MIN, MAX) can be used as a window function. This unlocks running totals and moving averages without cursors or subqueries.

Running total

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Running total reset per partition

SELECT
    user_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS user_running_total
FROM orders;

Moving average (last 7 days)

This is where the frame clause matters:

SELECT
    date,
    daily_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_revenue;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — the window includes the current row and the 6 rows before it. Classic 7-day rolling average.

ROWS vs RANGE

• ROWS — physical rows. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means exactly 6 rows back.
• RANGE — logical range based on the ORDER BY value. RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW means all rows within 6 days, regardless of row count.

For time series with gaps, RANGE is usually what you actually want.

-- ROWS: always exactly 6 rows back (may span more than 6 days if data is sparse)
AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- RANGE: all rows within the last 6 days (correct for sparse data)
AVG(revenue) OVER (ORDER BY date RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)

NTILE(): bucketing rows

Divides rows into N equal-sized buckets. Useful for percentiles, quartiles, and cohort analysis.

-- Divide customers into 4 quartiles by total spend
SELECT
    customer_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile
FROM customer_spend;

Quartile 1 = top spenders. Combine with a CTE to filter or label each group.

FIRST_VALUE() and LAST_VALUE()

Return the first or last value in the window frame.

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

LAST_VALUE() has a trap: the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so "last" means the current row, not the end of the partition. Fix it explicitly:

LAST_VALUE(name) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Reading execution plans

Window functions aren't free. Know what to look for in EXPLAIN ANALYZE.

EXPLAIN ANALYZE
SELECT
    user_id,
    order_date,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date)
FROM orders;

Look for:

• WindowAgg — the window function node. Normal.
• Sort — a sort before WindowAgg. May be expensive on large tables.
• Seq Scan vs Index Scan — if your PARTITION BY or ORDER BY columns aren't indexed, Postgres will sort the full table.

When to add an index

If the same PARTITION BY / ORDER BY combination appears in multiple queries:

CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);

This lets Postgres avoid a full sort and use the index order directly.

When NOT to use window functions

• Small datasets where a simple subquery is more readable
• When you need to filter on the window result in the same query level — you can't WHERE rank = 1 directly; you need a CTE or subquery
• When the frame logic gets complex enough that a CTE makes the intent clearer

Practical patterns worth bookmarking

Deduplicate keeping latest record

WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM events
)
SELECT * FROM ranked WHERE rn = 1;

Percent of total

SELECT
    department,
    SUM(salary) AS dept_total,
    SUM(salary) * 100.0 / SUM(SUM(salary)) OVER () AS pct_of_company
FROM employees
GROUP BY department;

Note: SUM(SUM(salary)) OVER () — a window function over an aggregate. This is valid SQL and very useful.

Gap detection between dates

SELECT
    user_id,
    order_date,
    LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_order,
    order_date - LAG(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS gap_days
FROM orders
WHERE gap_days > 30;  -- wrap in CTE to filter

Summary

Window functions don't replace every query pattern — but they replace the ugly ones. Self-joins, correlated subqueries, and cursor-based loops are often a window function with cleaner syntax and a better execution plan.

Learn the frame clause. It's the thing most tutorials skip, and it's where the real power is.

What's your most-used window function pattern in production? Drop it in the comments.

Tags: sql postgres backend database