Advanced SQL: Window Functions & CTEs
OVER, PARTITION BY, and frame clauses; ranking, running totals, and LAG/LEAD; the top-N-per-group pattern; recursive CTEs for hierarchies and date spines; gaps-and-islands; and when a CTE beats a subquery or a temp table
A practitioner's tour of the SQL constructs that separate 'knows SELECT' from 'can solve the hard interview problem': window function anatomy (OVER, PARTITION BY, ORDER BY, and the ROWS-vs-RANGE frame clause), ranking functions and when ROW_NUMBER, RANK, or DENSE_RANK is the correct choice, running totals and moving averages, LAG/LEAD for row-to-row comparisons, the top-N-per-group pattern, recursive CTEs for hierarchy traversal and date-spine generation with a full worked example, the gaps-and-islands problem solved without a loop, and a clear-eyed comparison of CTEs, subqueries, and temp tables for readability and optimizer behavior.
Practice questions (5)
-
View →
Top 3 Products Per Category, But the Numbers Look Wrong
Intermediate · Free -
View →
A 7-Day Moving Average That Isn't Actually 7 Days
Intermediate -
View →
A Recursive CTE for Reporting Chains Hangs in Production
Intermediate -
View →
Turning Overlapping Subscription Periods Into Merged Active Ranges
Intermediate -
View →
A Multi-Step CTE Pipeline Suddenly Got Slow After a Postgres Upgrade
Intermediate