Window Functions
Use OVER(), PARTITION BY, ROW_NUMBER, RANK, and LAG/LEAD to perform calculations across related rows without collapsing them.
- OVER
- PARTITION BY
- RANK
- ROW_NUMBER
- LAG
- LEAD
Use this when you need to:
- Add a running total to a dashboard — cumulative revenue by month, year-to-date enrollment
- Rank within groups — top 3 products per category, top student per cohort, fastest 5 lap times per driver
- Compare each row to the one before or after — month-over-month change with LAG, next-event lookup with LEAD
- Calculate percentiles or medians without collapsing the per-row detail
- Find each customer's first or most recent purchase without writing a self-join
Window functions perform calculations across a set of rows related to the current row — without collapsing rows the way GROUP BY does. The result is attached to each row individually.
Basic Syntax
-- aggregate_function(column) OVER (PARTITION BY ... ORDER BY ...)
SELECT
scs_student,
scs_course_name,
scs_credits,
SUM(scs_credits) OVER (
PARTITION BY scs_student -- restart the total for each student
ORDER BY scs_course_name -- the order rows are added to the total
) AS running_credits
FROM ods_stu_course_sec
WHERE scs_term = '2026SP';Ranking Functions
-- ROW_NUMBER: unique sequential number per partition
-- RANK: same rank for ties, gaps after ties
-- DENSE_RANK: same rank for ties, no gaps
SELECT
students_id,
stpr_gpa,
ROW_NUMBER() OVER (ORDER BY stpr_gpa DESC) AS row_num,
RANK() OVER (ORDER BY stpr_gpa DESC) AS rank,
DENSE_RANK() OVER (ORDER BY stpr_gpa DESC) AS dense_rank
FROM ods_stu_acad_levels
WHERE stpr_gpa IS NOT NULL -- DESC sorts NULLs first; rank only real GPAs
ORDER BY stpr_gpa DESC, students_id;LAG and LEAD
LAG accesses the previous row's value; LEAD accesses the next row's value. These are essential for retention and trend analysis. "Previous" means previous in the window's ORDER BY, so that order has to be the real calendar. A term code is not: '2024FA' sorts before '2024SP' as text, but Spring comes first. Join ods_terms and order by term_start_date.
WITH term_headcount AS (
-- registered students per term, with the date that puts terms in order
SELECT st.sttr_term, t.term_start_date, COUNT(*) AS headcount
FROM ods_student_terms st
LEFT JOIN ods_terms t ON t.terms_id = st.sttr_term
WHERE st.sttr_current_status = 'R'
GROUP BY st.sttr_term, t.term_start_date
)
SELECT
sttr_term,
headcount,
LAG(headcount) OVER (ORDER BY term_start_date) AS prev_term,
LEAD(headcount) OVER (ORDER BY term_start_date) AS next_term,
headcount - LAG(headcount) OVER (ORDER BY term_start_date) AS change
FROM term_headcount
ORDER BY term_start_date;PARTITION BY divides rows into groups before the window function runs — similar to GROUP BY but without collapsing. Use it to calculate per-student, per-term, or per-department running totals.
Practise this on real university data
Write real PostgreSQL in your browser and get it graded on the rows it returns. No signup and nothing to install for your first query.