Learn to run analytics without collapsing rows using PostgreSQL window functions. You will master OVER, PARTITION BY and ORDER BY, ranking with ROW_NUMBER/RANK/DENSE_RANK, row-to-row comparisons with LAG and LEAD, and running totals and moving averages with explicit frames.
Watch the free preview
What window functions are — the OVER clause — free to watch, no account needed.
What you'll learn
- Run analytics without collapsing rows using window functions and the OVER clause
- Partition and order rows to compute per-group values while keeping every detail row
- Rank rows within groups with ROW_NUMBER, RANK and DENSE_RANK
- Compare each row to its neighbours with LAG and LEAD
- Build running totals and moving averages with explicit ROWS and RANGE frames
Syllabus
Window Function Fundamentals — OVER and PARTITION BY
What window functions are — the OVER clauseFree preview
PARTITION BY — per-group calculations that keep every row
Ranking Within Groups
ROW_NUMBER, RANK and DENSE_RANK
Ranking within groups and top-N per partition
Navigation — LAG, LEAD and Positional Functions
LAG and LEAD — comparing to neighbouring rows
Positional functions — FIRST_VALUE, LAST_VALUE, NTILE
Running Totals, Moving Averages and Framing
Running totals and cumulative aggregates
Framing — ROWS versus RANGE and moving averages
Lab — 10 Exercises & Solutions
Exercises 1–5
Exercises 6–10