Window Functions

What we're doing: Show every employee row alongside their department's average salary, without collapsing any rows the way GROUP BY would.

CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, dept TEXT, salary NUMERIC);

INSERT INTO employees (name, dept, salary) VALUES
    ('Ada', 'Engineering', 120000),
    ('Grace', 'Engineering', 110000),
    ('Linus', 'Sales', 90000);

SELECT name, dept, salary,
    avg(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

3–6

Two departments: Engineering (2 employees) and Sales (1 employee).

8

avg(salary) OVER (PARTITION BY dept) computes each row's department average without collapsing the 3 rows into 2.

name  | dept        | salary | dept_avg
------+-------------+--------+----------
Ada   | Engineering | 120000 | 115000.0
Grace | Engineering | 110000 | 115000.0
Linus | Sales       |  90000 |  90000.0
(3 rows)

Why this works: PARTITION BY dept groups the underlying rows exactly like GROUP BY would, but a window function computes its aggregate per partition and then attaches that value to every original row in the partition, rather than replacing the rows with one summary row per group. Ada and Grace both see 115000.0 because they share the Engineering partition, while Linus sees his own salary as the Sales partition's average since he is its only member — all three original employee rows survive.

What we're doing: Compare ROW_NUMBER, RANK and DENSE_RANK on data with a tie, then use LAG to see the previous row's score.

CREATE TABLE results (name TEXT, score INT);

INSERT INTO results (name, score) VALUES
    ('Ada', 95), ('Grace', 90), ('Linus', 90), ('Margaret', 80);

SELECT name, score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
    RANK() OVER (ORDER BY score DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk,
    LAG(score) OVER (ORDER BY score DESC) AS prev_score
FROM results;

4

Grace and Linus are tied at 90 — the interesting case for RANK vs DENSE_RANK.

8–9

RANK gives the tied pair both rank 2, then skips to 4 for Margaret; ROW_NUMBER never ties, giving 2 and 3.

10

DENSE_RANK gives the tied pair both rank 2, then continues at 3 with no gap.

11

LAG(score) shows each row's predecessor in the ORDER BY sequence — NULL for the first row, since it has no predecessor.

name     | score | rn | rnk | dense_rnk | prev_score
---------+-------+----+-----+-----------+-----------
Ada      |    95 |  1 |   1 |         1 |           
Grace    |    90 |  2 |   2 |         2 |         95
Linus    |    90 |  3 |   2 |         2 |         90
Margaret |    80 |  4 |   4 |         3 |         90

Why this works: RANK() assigns the same rank to tied rows but still counts them individually toward the next rank, so two rows tied at rank 2 push the following row to rank 4 (2 tied rows occupy ranks 2 AND 3, leaving 4 as the next number) — DENSE_RANK() counts only distinct rank values, so the same tie is followed immediately by rank 3. LAG(score) with no explicit offset always means "the immediately preceding row in this ORDER BY," which is why Ada's row shows NULL: there is no row before the first one.

What we're doing: Compute a running total of payments over time, then separately find the top 2 highest-paid employees per department.

CREATE TABLE payments (pay_date DATE, amount NUMERIC);
INSERT INTO payments VALUES ('2026-01-01', 100), ('2026-01-02', 50), ('2026-01-03', 75);

SELECT pay_date, amount, SUM(amount) OVER (ORDER BY pay_date) AS running_total
FROM payments;

CREATE TABLE employees (name TEXT, dept TEXT, salary NUMERIC);
INSERT INTO employees VALUES
    ('Ada', 'Eng', 150000), ('Grace', 'Eng', 140000), ('Linus', 'Eng', 130000),
    ('Margaret', 'Sales', 90000);

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, dept, salary FROM ranked WHERE rn <= 2;

4

SUM with ORDER BY (no PARTITION BY) defaults to a frame of "everything from the start up to this row" — a running total.

12–15

ROW_NUMBER inside a CTE assigns per-department rank; the outer query filters to rn <= 2, since a window function cannot be filtered directly in its own SELECT's WHERE.

pay_date   | amount | running_total
-----------+--------+---------------
2026-01-01 |    100 |           100
2026-01-02 |     50 |           150
2026-01-03 |     75 |           225
(3 rows)

name  | dept | salary
------+------+--------
Ada   | Eng  | 150000
Grace | Eng  | 140000
(2 rows)

Why this works: SUM() OVER (ORDER BY pay_date) with no explicit frame defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — every row sums itself plus every row before it in the ordering, which is exactly the definition of a running total. Linus and Margaret are excluded from the top-N result not because of a WHERE clause on salary directly, but because rn is computed per department first (Linus ranks 3rd in Engineering, Margaret ranks 1st in Sales but Sales only appears once in the source data) — the CTE is required specifically because WHERE cannot see a window function's result computed in the same SELECT.

What we're doing: Compute a 3-row moving average with an explicit ROWS frame, and compare it against the default RANGE-based running total.

CREATE TABLE payments (pay_date DATE, amount NUMERIC);
INSERT INTO payments VALUES
    ('2026-01-01', 100), ('2026-01-02', 50), ('2026-01-03', 75), ('2026-01-04', 200);

SELECT pay_date, amount,
    SUM(amount) OVER (ORDER BY pay_date) AS running_total,
    AVG(amount) OVER (
        ORDER BY pay_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3day
FROM payments;

5

Default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW): every row sums itself plus every prior row.

7–10

Explicit ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: exactly 3 physical rows (or fewer near the start), never the whole history.

pay_date   | amount | running_total | moving_avg_3day
-----------+--------+---------------+----------------
2026-01-01 |    100 |           100 |          100.0
2026-01-02 |     50 |           150 |           75.0
2026-01-03 |     75 |           225 |           75.0
2026-01-04 |    200 |           425 |          108.3
(3 rows)

Why this works: running_total keeps growing across all 4 rows because its frame is unbounded on the preceding side, so the 4th row still includes the 1st row's amount. moving_avg_3day on the 4th row averages only rows 2, 3 and 4 (50, 75, 200), because ROWS BETWEEN 2 PRECEDING AND CURRENT ROW explicitly caps the frame at 3 physical rows regardless of how much history exists — the 1st row has already aged out of the window by the time the 4th row is processed.

What we're doing: Write "each employee vs their department's highest salary" both as a self join and as a window function, and compare the queries.

CREATE TABLE employees (name TEXT, dept TEXT, salary NUMERIC);
INSERT INTO employees VALUES
    ('Ada', 'Eng', 150000), ('Grace', 'Eng', 140000), ('Linus', 'Sales', 90000);

-- self join / subquery version
SELECT e.name, e.salary, d.max_salary
FROM employees e
JOIN (
    SELECT dept, max(salary) AS max_salary FROM employees GROUP BY dept
) d ON d.dept = e.dept;

-- window function version -- same result, no join, one pass
SELECT name, salary, MAX(salary) OVER (PARTITION BY dept) AS max_salary
FROM employees;

5–9

The self join needs a derived table (GROUP BY dept), then an explicit JOIN back to employees on dept.

12–13

The window function expresses the same relationship with PARTITION BY dept — no derived table, no explicit join.

name  | salary | max_salary
------+--------+-----------
Ada   | 150000 |     150000
Grace | 140000 |     150000
Linus |  90000 |      90000
(3 rows)

Why this works: Both queries produce the identical result set because they express the same underlying relationship — "compare this row to an aggregate of its own group" — but the self join version has to physically materialize a derived table and join it back, while the window function version lets the engine compute the per-partition aggregate in a single pass and attach it directly to each row. This is why the window function version is both shorter to write and typically the plan the optimizer favors for this exact shape of question.