SQL: window functions for BI
This course covers Postgres window functions through one running example: a learner percentile that this app once computed with a GROUP BY and HAVING query per learner. It explains how the OVER clause computes across rows without collapsing them, and how RANK, DENSE_RANK and ROW_NUMBER number ties. It shows how PERCENT_RANK gives every learner's percentile in one query. It also covers the default frame behind running totals, PARTITION BY for per-course rankings, and LAG for the time between events.
What you'll learn
- ✓How OVER differs from GROUP BY and keeps every input row
- ✓How RANK, DENSE_RANK and ROW_NUMBER handle tied rows
- ✓Computing every learner's percentile with PERCENT_RANK over a CTE of totals
- ✓How the default RANGE frame affects running totals, and when to write ROWS with a tiebreaker
- ✓Per-group rankings with PARTITION BY and gaps between events with LAG
One query per user, just to rank them
Enrollment::UserGamificationQuery computes a number this app shows each learner: leaderboard_percentile, how their XP stacks up against everyone else's. Until a fix shipped on the day this course was written, the code that computed it read:
Read the full lesson free →Curriculum
7 lessonsOne query per user, just to rank them
How the app computed leaderboard percentiles with one GROUP BY query per learner, and why window functions fit better.
OVER: the clause that doesn't collapse rows
Adding OVER to an aggregate keeps every input row, and window functions run after GROUP BY in a query.
RANK, DENSE_RANK, ROW_NUMBER: three ways to number a tie
How RANK, DENSE_RANK and ROW_NUMBER number tied rows, and which question each ranking function answers.
PERCENT_RANK: every percentile in one query
Using PERCENT_RANK over a CTE of totals to compute every learner's percentile in a single pass.
Running totals, and the frame nobody writes explicitly
The default RANGE frame gives tied rows the same running total, so write ROWS and add a tiebreaker.
PARTITION BY and LAG: per-group windows, and time between events
PARTITION BY restarts rankings per course, and LAG reads the previous row to measure time between events.
Putting it together: one query, every learner ranked
One closing query combines a totals CTE, RANK, PERCENT_RANK and PARTITION BY into a per-course leaderboard.