SQL Mastery: Window Functions, Indexing & Query Optimisation
Rank, compare and accumulate rows with window functions, walk hierarchies with recursive CTEs, and estimate index, scan and join costs like a query planner, with every answer checked as you go.
- One-time payment
- Instant access right after payment
- Secure checkout with Stripe
About this course
Go beyond GROUP BY. In 21 interactive labs you read real SQL and work out exactly what it returns: leaderboards with ROW_NUMBER, RANK and DENSE_RANK, top-N per group, LAG and LEAD growth, running totals, moving averages and the ROWS vs RANGE frame trap, NTILE and percentiles, gaps and islands, and recursive CTEs for series and hierarchies. Then you think like a query planner: estimate selectivity, B-tree height, index versus full scan, composite index prefixes, sargable predicates, nested loop versus hash join, normalisation and isolation levels. Every answer is checked instantly, and a final exam earns your certificate.
Lessons
- ROW_NUMBER(): Numbering Rows Without Losing ThemFree
- PARTITION BY: Top-N per Group Locked
- RANK, DENSE_RANK and ROW_NUMBER on Ties Locked
- LAG and LEAD: Comparing with the Previous Row Locked
- Running Totals with SUM() OVER Locked
- Window Frames: Moving Averages with ROWS BETWEEN Locked
- ROWS vs. RANGE: The Default Frame Trap Locked
- Partition Totals and Shares Without GROUP BY Locked
- NTILE, PERCENT_RANK and CUME_DIST Locked
- FIRST_VALUE, LAST_VALUE and the Frame Trap Locked
- Gaps and Islands: Finding Streaks Locked
- Recursive CTEs: Generating a Series Locked
- Recursive CTEs: Walking a Hierarchy Locked
- Index Selectivity and Optimizer Statistics Locked
- B-tree Height and the Index vs. Scan Decision Locked
- Composite Indexes and the Leftmost-Prefix Rule Locked
- Sargable Predicates and Date Ranges Locked
- Join Algorithms: Nested Loop vs. Hash Join Locked
- Normalisation: Redundancy, Anomalies and Size Locked
- Transactions, Lost Updates and Isolation Levels Locked
- Final Project: A Regional Revenue Report Locked