Window functions

Window functions compute across a set of rows without collapsing them - a rank beside each row, a running total, the previous row’s value. CTEs, which used to share this note, are now in CTEs and recursive queries.

Window function anatomy

SELECT
    user_id,
    created_at,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total
FROM transactions;

• OVER (...) is what makes it a window function. Without OVER, SUM is an aggregate that collapses rows.
• PARTITION BY — split into independent windows (like GROUP BY but rows aren’t merged).
• ORDER BY — order within each partition. Required for ranking and running aggregates.

Ranking functions

-- Top 3 most expensive products per category
SELECT * FROM (
    SELECT
        category, name, price,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
    FROM products
) ranked
WHERE rn <= 3;

This is the canonical “top-N per group” pattern — much cleaner than JOIN against a MAX(price) subquery.

LAG and LEAD

Reach into adjacent rows.

SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) AS delta
FROM daily_revenue;

• LAG(col, n, default) — value n rows before in the window.
• LEAD(col, n, default) — value n rows after.

Running aggregates and frames

By default, ORDER BY in a window means “rows from start of partition through current row” (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Override with an explicit frame:

-- 7-day moving average
SELECT
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS avg_7d
FROM daily_revenue;

• ROWS — physical row count.
• RANGE — logical value range (e.g., date-based).
• GROUPS — peer groups (PG 11+).

CTEs and recursion

Common table expressions, the optimisation fence, and WITH RECURSIVE are covered in depth in CTEs and recursive queries - the fence in particular has bitten more people than window syntax ever will.

Common patterns

Pagination with stable ordering:

SELECT *, ROW_NUMBER() OVER (ORDER BY created_at, id) AS rn FROM events;
-- ORDER BY created_at alone is not stable on duplicates; add id as tiebreaker.

Deduplicate keeping latest:

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM users
) t WHERE rn = 1;

Gap and island detection (consecutive runs):

SELECT user_id, MIN(date), MAX(date)
FROM (
    SELECT user_id, date,
           date - INTERVAL '1 day'
               * ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) AS grp
    FROM activity
) t
GROUP BY user_id, grp;