SQL Window Functions in a Spreadsheet: Running Totals, Ranks per Group, Previous-Row Values, Moving Averages & Sessions
Learn what OVER and PARTITION BY really do by building each window function as a formula: running totals, ranks per group, previous and next rows, moving averages and user sessions, with every cell checked as you go.
One-time payment · lifetime access · lessons, labs, exam and certificate included · no subscription.
About this course
This course explains SQL window functions by rebuilding each one as a spreadsheet formula on a fictional coffee shop's data. You put customer totals next to every order with SUMIFS (PARTITION BY), build running totals and see how ties behave in RANGE and ROWS frames, rank products per category with RANK, DENSE_RANK and ROW_NUMBER logic, fetch previous and next orders like LAG and LEAD with MAXIFS and MINIFS, compare 7-day and 7-row moving averages, sort customers into NTILE quartiles and percent ranks, find first and latest orders with FIRST_VALUE and LAST_VALUE, and finally turn a click log into user sessions with gap flags and running session numbers.
Lessons
- PARTITION BY vs GROUP BY: Group Totals Next to Every Row Pro
- Running Totals: Cumulative Sums per Customer, Ties & the Running Share Pro
- ROW_NUMBER, RANK & DENSE_RANK per Group: Ties and Top-n Pro
- LAG & LEAD: Previous and Next Order, Gaps and Changes Pro
- Moving Averages & Rolling Windows: Date Ranges vs Row Counts Pro
- NTILE & PERCENT_RANK: Quartiles, Percentiles and Halves per Group Pro
- FIRST_VALUE & LAST_VALUE: First Orders, Cohort Labels and the Latest Row Pro
- Capstone: Sessionisation – Gaps, New-Session Flags and Session Numbers Pro