PostgreSQL Window Functions in Practice

Window functions are a seriously underrated SQL feature. When many developers encounter requirements such as "the top 3 in each category," "cumulative sales," or "period-over-period and year-over-year comparisons," their first instinct is to nest a subquery inside GROUP BY or simply pull the data into the application layer and calculate it with a Python or Java loop. The resulting SQL is long, slow, and easy to get wrong.

In fact, a single window function can solve these problems. This article explains window functions thoroughly, from syntax to practical use, so you can apply them directly in production SQL.

1. Why GROUP BY Falls Short

Suppose you have an orders table:

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    amount      NUMERIC(12,2) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL
);

The product team says, "I want to see each user's total spending while retaining the details of every order."

With GROUP BY, you might write this:

SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id;

The result has only one row per user, so the details are gone. To retain them, you have to join the result back:

SELECT o.*, t.total
FROM orders o
JOIN (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) t ON t.user_id = o.user_id;

It works, but it is ugly. If the next requirement is "the top three orders by spending for each user," GROUP BY is no longer enough. You would need a self-join or have to calculate the result outside the database.

Window functions solve exactly this conflict: perform calculations across rows without collapsing those rows.

2. Core Syntax: The Three Parts of OVER

The general form of a window function is:

function(arguments) OVER (
    PARTITION BY <grouping_column>
    ORDER BY     <sorting_column>
    ROWS BETWEEN <start> AND <end>
)

Understand these three parts and you understand the core concept:

• PARTITION BY: Groups rows like GROUP BY, but does not collapse them. It divides the table into multiple "windows," each calculated independently.
• ORDER BY: Sorts rows within a group. Note that it changes the semantics of an aggregate. Without ORDER BY, SUM calculates the whole group; with ORDER BY, it defaults to accumulating from the start of the group through the current row.
• ROWS BETWEEN (the frame): Defines how many rows each calculation looks backward and forward. This is the key to moving averages and rolling sums.

All three are optional. The simplest form, SUM(amount) OVER (), calculates a global total and attaches it to every row.

3. The Ranking Trio: ROW_NUMBER / RANK / DENSE_RANK

The most common requirement is "the top N in each group." These three functions handle ties differently:

SELECT
    user_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,
    RANK()       OVER (PARTITION BY user_id ORDER BY amount DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS drnk
FROM orders;

• ROW_NUMBER(): Always assigns 1, 2, 3, 4, giving tied rows different numbers.
• RANK(): Gives tied rows the same number and leaves gaps afterward (1, 2, 2, 4).
• DENSE_RANK(): Gives tied rows the same number without leaving gaps afterward (1, 2, 2, 3).

The standard pattern for "the top 3 in each group" is to wrap the query in a subquery and filter it:

SELECT * FROM (
    SELECT o.*,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders o
) t
WHERE rn <= 3;

This is a common interview question and a frequent production use case. Remember one rule: a window function cannot be used directly in WHERE; you must wrap it in a subquery, because window functions are evaluated after WHERE.

4. Running and Moving Aggregates

After you add ORDER BY, SUM, AVG, and COUNT become running calculations from the start of the group through the current row:

SELECT
    created_at::date AS d,
    amount,
    SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM orders;

This produces cumulative sales.

For a three-day moving average, explicitly control the range with a frame:

SELECT
    created_at::date AS d,
    amount,
    AVG(amount) OVER (
        ORDER BY created_at::date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS ma3
FROM orders;

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means "the current row plus the two preceding rows." Other frame boundaries include UNBOUNDED PRECEDING (the start of the group), UNBOUNDED FOLLOWING (the end of the group), and CURRENT ROW. With ORDER BY, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is why adding ORDER BY automatically produces a running calculation.

One easily confused detail is that ROWS counts physical rows, while RANGE operates on sort values, including rows with equal values together. Use ROWS for 95% of everyday cases because its behavior is predictable.

5. Offsets: Using LAG / LEAD for Period Comparisons

LAG retrieves data before the current row, while LEAD retrieves data after it, making them a natural fit for period-over-period and year-over-year comparisons:

SELECT
    created_at::date AS d,
    amount                                  AS today,
    LAG(amount, 1) OVER (ORDER BY created_at) AS yesterday,
    amount - LAG(amount, 1) OVER (ORDER BY created_at) AS diff,
    ROUND(
        (amount - LAG(amount, 1) OVER (ORDER BY created_at))
        / NULLIF(LAG(amount, 1) OVER (ORDER BY created_at), 0) * 100,
        2
    ) AS growth_pct
FROM orders;

The second argument is the row offset, which defaults to 1, and the third is the default value to use when the row does not exist. Using NULLIF to prevent division by zero is an essential engineering detail, so do not omit it.

To compare against the same month in the previous year, replace LAG(amount, 1) with LAG(amount, 12) OVER (ORDER BY month). This is far cleaner than a self-join such as JOIN orders o2 ON o2.month = o.month - 12 and much harder to get wrong.

6. First and Last Values and Percentiles: FIRST_VALUE / NTILE / PERCENT_RANK

-- Amount of each user's first order
SELECT DISTINCT user_id,
    FIRST_VALUE(amount) OVER (
        PARTITION BY user_id ORDER BY created_at
    ) AS first_amount
FROM orders;

-- Divide spending amounts into four tiers (quartiles)
SELECT user_id, amount,
    NTILE(4) OVER (ORDER BY amount) AS quartile
FROM orders;

-- Percentile rank within each group
SELECT user_id, amount,
    PERCENT_RANK() OVER (PARTITION BY user_id ORDER BY amount) AS pct
FROM orders;

LAST_VALUE has a classic pitfall: the default frame ends at the current row. To get the last value in the group, you must explicitly specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING; otherwise, it always returns the current row's own value. Once you encounter this pitfall, you will remember it.

7. Window Functions vs. GROUP BY: The Fundamental Difference

In one sentence: GROUP BY collapses N rows into one, while a window function retains all N rows and gives each one a result.

Remember this rule of thumb: if every row in the desired result needs its own value, you probably need a window function. If you only need one summary value per group, GROUP BY is enough.

8. Performance: The Advantage of a Single Scan

In an execution plan, a window function usually scans the data only once, sorts it by the PARTITION BY columns, and then calculates the result as a stream. By comparison, a self-join such as JOIN ... ON a.user_id = b.user_id AND a.id >= b.id can easily be optimized into a nested loop with near-O(N²) behavior. Window functions are often an order of magnitude faster.

Practical advice for large datasets:

1. Create a composite index for the PARTITION BY and ORDER BY combination. For example, PARTITION BY user_id ORDER BY created_at DESC corresponds to CREATE INDEX ON orders(user_id, created_at DESC). The execution plan can use an ordered index scan directly and eliminate the Sort node.
2. Do not overuse window functions. When the problem is fundamentally a pure aggregation and you only need one result per group, a straightforward GROUP BY is usually faster and uses less memory. A window function still has to retain state for every row.
3. Verify with EXPLAIN ANALYZE. If you see a high-cost Sort before WindowAgg, the index probably was not used.
4. Watch memory usage for large windows. Larger frames and partitions create more memory pressure. Increasing work_mem appropriately can avoid spilling to disk; otherwise, sorting and file writes can cause performance to collapse.

9. Three Practical Examples

Example 1: Each user's three most recent orders

SELECT * FROM (
    SELECT o.*,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders o
    WHERE created_at >= NOW() - INTERVAL '90 days'
) t
WHERE rn <= 3;

Note that WHERE runs before the window function. Filtering early can greatly reduce the amount of data that must be sorted, making this a common optimization technique.

Example 2: Cumulative daily PV

SELECT
    day,
    pv,
    SUM(pv) OVER (ORDER BY day) AS cumulative_pv
FROM daily_pv;

Example 3: Period-over-period and year-over-year comparisons in one table

WITH monthly AS (
    SELECT date_trunc('month', created_at) AS mon,
           SUM(amount) AS total
    FROM orders
    GROUP BY 1
)
SELECT mon,
    total,
    LAG(total, 1)  OVER (ORDER BY mon) AS prev_month,   -- Month-over-month
    LAG(total, 12) OVER (ORDER BY mon) AS prev_year     -- Year-over-year
FROM monthly;

GROUP BY and window functions work together: first use GROUP BY to calculate monthly totals, then use LAG for time offsets. This is a classic combination in reporting systems. The two are collaborators, not substitutes.

Summary

• Window function = cross-row calculation + no row collapsing. This is the fundamental distinction from GROUP BY.
• Three components: PARTITION BY groups rows, ORDER BY sorts them and changes aggregate semantics, and ROWS BETWEEN defines the frame.
• Five major use cases: ranking, running and moving aggregates, offsets for period comparisons, first and last values, and percentile buckets. Each has corresponding functions.
• Cannot be used directly in WHERE. Wrap the query in a subquery to filter window results.
• Usually faster than a self-join. For large datasets, create a composite index on the PARTITION BY and ORDER BY columns.

A memorable line: GROUP BY looks at the "face of the group," while a window function looks at the "context of the row." Both aggregate data, but one flattens it while the other broadens the view.

Once you master window functions, you will find that many ugly subqueries, application-layer for loops, and self-joins you wrote in the past can be deleted and rewritten as a single SQL statement. This is not only about reducing code. It is also a shift in thinking from the row level to the window level. Once you get used to it, you will not want to go back.