A module-by-module concept outline. Open the course to learn each topic with animated explanations, in-browser code, practice challenges, and a knowledge check.

Module 1. The Mental Model: OVER, PARTITION BY, ORDER BY

Free

Topics

Why window functions existThe OVER() clause anatomyPARTITION BY — slicing the dataORDER BY in OVER — sequencing the slice

Sections

1Why Window Functions Exist
2Anatomy of OVER() — The Three Dials
3PARTITION BY — Many Windows, One Query
4ORDER BY in OVER — Sequence Matters
5Where Windows Run in the Pipeline
6NULLs in ORDER BY Inside OVER

Module 2. Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE

Pro

Topics

ROW_NUMBER — unique sequential ranksRANK — competition ranks with gapsDENSE_RANK — tiers without gapsNTILE — equal-sized buckets

Sections

1ROW_NUMBER — The Default Rank
2RANK vs DENSE_RANK — Handling Ties
3NTILE — Bucketing into Quartiles & Deciles
4Top-N Per Group — The Interview Pattern
5ROW_NUMBER for Dedup — The One Canonical Recipe

Module 3. Aggregate Window Functions

Pro

Topics

SUM/AVG/COUNT/MIN/MAX with OVERRunning totalsGrand totals & percent-of-totalMultiple windows in one query

Sections

1Aggregate-as-Window — The Mental Switch
2Running Totals & Cumulative Sums
3Percent of Total & Share Analysis
4Combining Multiple Windows in One Query
5FILTER Previews & the COUNT(DISTINCT) Caveat
6Mixing Aggregates with Ranking — A Preview

Module 4. LAG, LEAD & Comparing Rows

Pro

Topics

LAG — look at the previous rowLEAD — look at the next rowPeriod-over-period growthGap & sequence analysis

Sections

1LAG — Looking Backward
2LEAD — Looking Forward
3Period-Over-Period Growth
4Gap Analysis & Sequence Patterns
5LAG With Offsets & The Forward-Fill Trap
6Production Pattern — Cohort Retention

Module 5. Frame Clauses: ROWS vs RANGE

Pro

Topics

Default frames — and why they biteROWS — physical row countingRANGE — logical value countingMoving averages and centered windows

Sections

1The Default Frame & Why It Bites
2ROWS — Counting Physical Positions
3RANGE — Counting Logical Values
4Moving Averages & Centered Frames
5EXCLUDE Clauses & Numeric RANGE BETWEEN
6The Five Mistakes Reviewers Catch in PRs

Module 6. Multi-Dialect Reality: SQLite vs PostgreSQL

Pro

Topics

The SQL standard vs vendor realityPostgreSQL extensions: FILTER, IGNORE NULLSRANGE BETWEEN INTERVAL — only in some dialectsQUALIFY (BigQuery / Snowflake) and workarounds

Sections

1Why Dialects Diverge
2FILTER & IGNORE NULLS — Postgres Superpowers
3NULLS FIRST / NULLS LAST — The Silent Cross-Dialect Trap
4RANGE Intervals — A PostgreSQL-Only Trick
5QUALIFY & the WHERE-on-Windows Workaround
6The Portability Decision Tree

Module 7. FIRST_VALUE, LAST_VALUE, NTH_VALUE & FILTER

Pro

Topics

FIRST_VALUE & LAST_VALUE — and the LAST_VALUE trapNTH_VALUE — sampling the Nth rowPERCENT_RANK & CUME_DISTConditional aggregation inside windows

Sections

1FIRST_VALUE — Anchoring to the Start
2LAST_VALUE — And the Frame Trap
3NTH_VALUE, PERCENT_RANK, CUME_DIST
4PERCENTILE_CONT & PERCENTILE_DISC for Median & p95
5Conditional Aggregation Inside Windows
6Sessionization — The Itzik Ben-Gan Recipe
7Production Pattern — Cohort Retention Heatmap

Module 8. Performance & Anti-Patterns

Pro

Topics

Windows vs self-joins — when each winsCommon anti-patterns and how to spot themEXPLAIN reading for window queriesRefactoring slow analytics queries

Sections

1Windows vs Self-Joins — When Each Wins
2Anti-Patterns You Will See in Production
3Reading EXPLAIN for Window Queries
4Refactoring Slow Analytics Queries
5Capstone — Every Module in One Query

SQL Window Functions

What this course covers

Module 1. The Mental Model: OVER, PARTITION BY, ORDER BY

Module 2. Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE

Module 3. Aggregate Window Functions

Module 4. LAG, LEAD & Comparing Rows

Module 5. Frame Clauses: ROWS vs RANGE

Module 6. Multi-Dialect Reality: SQLite vs PostgreSQL

Module 7. FIRST_VALUE, LAST_VALUE, NTH_VALUE & FILTER

Module 8. Performance & Anti-Patterns

Ready to start SQL Window Functions?

All course syllabi