Advanced SQL: Window Functions, CTEs and Query Optimisation
The SQL that separates junior analysts from senior ones.
Taught by Sabina Tamang · Data Engineer
What you'll learn
- Write self joins, anti joins and set operations that answer questions a simple join cannot
- Use scalar, correlated and EXISTS subqueries and know when each is the clearest choice
- Structure multi-step queries with CTEs and solve hierarchies with recursive CTEs
- Apply ranking, offset and aggregate window functions with correct PARTITION BY and frames
- Read EXPLAIN ANALYZE output to identify sequential scans, bad join orders and estimate errors
- Design indexes and rewrite queries so they run in milliseconds instead of seconds
About this course
Move beyond SELECT, WHERE and GROUP BY into the advanced SQL that analytics and data engineering interviews test. This intermediate course covers joins in depth including self joins, anti joins and set operations; scalar, correlated and EXISTS subqueries; common table expressions (CTEs) including recursive queries; window functions such as ROW_NUMBER, RANK, LAG, LEAD and running totals with explicit frames; reading EXPLAIN ANALYZE output; and using indexes to make slow queries fast. Examples run on PostgreSQL 18 and note the differences for MySQL, SQL Server, BigQuery and Snowflake. You should already be comfortable writing basic queries with aggregates and simple joins. Each lesson ends with interview-style problems, and the final lesson works through the classic questions, such as top N per group, gaps and islands, and deduplication, that separate junior analysts from senior ones.
Course content
7 lessons- 1Joins Beyond the BasicsSelf joins, anti joins, semi joins and set operations for questions a plain join cannot answer.Preview
- 2Subqueries: Scalar, Correlated and EXISTSNest queries in SELECT, FROM and WHERE, and know when a subquery beats a join.
- 3CTEs and Recursive QueriesBreak complex queries into named steps with WITH, and walk hierarchies with recursive CTEs.
- 4Window Functions: Ranking and OffsetsROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG and LEAD with PARTITION BY and ORDER BY.
- 5Window Frames, Running Totals and Moving AveragesControl exactly which rows an aggregate window sees with ROWS and RANGE frames.
- 6Reading EXPLAIN and Designing IndexesFind out why a query is slow with EXPLAIN ANALYZE, then fix it with the right index or rewrite.
- 7Interview-Style ProblemsWork through the classic questions: top N per group, deduplication, gaps and islands, retention and running metrics.