Window Functions

Window functions calculate across a set of related rows without collapsing them like GROUP BY does — every row keeps its detail and gains a computed value. The OVER (...) clause defines the window.

PARTITION BY splits rows into groups (per subject, per student), and ORDER BY inside OVER sets the order for ranks and running totals.

Key functions: rank() / dense_rank() / row_number() for positions, avg() / sum() over a window for group figures and running totals, and lag() / lead() to compare with the previous or next row.

windows.sqlSQL

-- Position in each subject for term 2
SELECT sub.name AS subject, s.full_name, r.score,
       rank() OVER (PARTITION BY r.subject_id ORDER BY r.score DESC) AS position
FROM results r
JOIN students s   ON s.id = r.student_id
JOIN subjects sub ON sub.id = r.subject_id
WHERE r.term = 2
ORDER BY subject, position;

-- Each score next to the subject average and the difference
SELECT s.full_name, r.subject_id, r.score,
       round(avg(r.score) OVER (PARTITION BY r.subject_id, r.term), 1) AS subject_avg,
       r.score - round(avg(r.score) OVER (PARTITION BY r.subject_id, r.term), 1) AS vs_avg
FROM results r
JOIN students s ON s.id = r.student_id
WHERE r.term = 1
ORDER BY r.subject_id, r.score DESC;

-- Change since last term with lag()
SELECT s.full_name, r.subject_id, r.term, r.score,
       r.score - lag(r.score) OVER (PARTITION BY r.student_id, r.subject_id ORDER BY r.term) AS change
FROM results r
JOIN students s ON s.id = r.student_id
WHERE r.subject_id = 1
ORDER BY s.full_name, r.term;

-- Running total of fee payments over time
SELECT paid_at::date AS day, amount,
       sum(amount) OVER (ORDER BY paid_at) AS running_total
FROM payments
ORDER BY paid_at;

-- Top 2 per subject: rank in a CTE, then filter
WITH ranked AS (
    SELECT r.*, row_number() OVER (PARTITION BY subject_id ORDER BY score DESC) AS rn
    FROM results r WHERE term = 2
)
SELECT subject_id, student_id, score FROM ranked WHERE rn <= 2 ORDER BY subject_id, rn;

Runs in your browser · PostgreSQL

Key points

Window functions add per-group values without collapsing rows.
PARTITION BY = the groups; ORDER BY in OVER = order for ranks/running totals.
Filter on a window result by wrapping it in a CTE or subquery.

Exercise

Produce a class report for Form 4: each student's overall term-2 average, their position in the class (dense_rank), and the difference from the student just above them (lag).

Show solution

Try the exercise yourself first — then compare your approach with this one.

Average each Form 4 student's term-2 scores in a CTE. dense_rank gives class positions without gaps for ties, and lag (ordered the same way) fetches the average of the student just above, so the difference shows how far behind each student is.

class-report.sqlSQL

WITH term2 AS (
    SELECT s.full_name, round(avg(r.score), 1) AS average
    FROM results r
    JOIN students s ON s.id = r.student_id
    WHERE s.form = 4 AND r.term = 2
    GROUP BY s.full_name
)
SELECT full_name,
       average,
       dense_rank() OVER (ORDER BY average DESC) AS position,
       lag(average) OVER (ORDER BY average DESC) - average AS behind_student_above
FROM term2
ORDER BY position;

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding