PostgreSQL Window Functions - PostgreSQL Tutorial

A window function computes across a set of rows without collapsing them — unlike GROUP BY, every input row still appears in the output.

SQL

SELECT
    title,
    author,
    price,
    AVG(price) OVER (PARTITION BY author) AS author_avg
FROM books;

Each book keeps its own row and gains its author's average.

Ranking

SQL

SELECT
    title,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
    RANK()       OVER (ORDER BY price DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM books;

Function	On ties
`ROW_NUMBER`	1, 2, 3, 4 — always distinct
`RANK`	1, 2, 2, 4 — leaves gaps
`DENSE_RANK`	1, 2, 2, 3 — no gaps

Top N per group

The classic use — "the two most expensive books per author":

SQL

WITH ranked AS (
    SELECT
        title, author, price,
        ROW_NUMBER() OVER (PARTITION BY author ORDER BY price DESC) AS rn
    FROM books
)
SELECT title, author, price
FROM ranked
WHERE rn <= 2;

You cannot filter on a window function in WHERE directly — it is computed after WHERE runs — hence the CTE.

LAG and LEAD

Compare a row with its neighbours:

SQL

SELECT
    month,
    revenue,
    LAG(revenue)  OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_sales
ORDER BY month;

Running totals

SQL

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

Named windows

When several functions share a window, define it once:

SQL

SELECT
    title,
    ROW_NUMBER() OVER w AS rn,
    SUM(price)   OVER w AS running
FROM books
WINDOW w AS (PARTITION BY author ORDER BY published);

Other useful ones

SQL

FIRST_VALUE(price) OVER (PARTITION BY author ORDER BY published)
LAST_VALUE(price)  OVER (PARTITION BY author ORDER BY published
                         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
NTILE(4) OVER (ORDER BY price)     -- quartiles
PERCENT_RANK() OVER (ORDER BY price)

LAST_VALUE needs that explicit frame clause — the default frame stops at the current row, which surprises everyone the first time.