Andrei V.
Sample Gaps and islands finally clicked
The hand worked table for the row number difference trick made it obvious. I used it for login streaks the same week.
Advanced SQL: window functions, CTEs and query tuning
Solve hard analytical problems in SQL, from gaps and islands to recursive CTEs, and make slow queries fast by reading query plans
Created by Aiko Tanaka
- Updated September 2026
- English
- Advanced
What you will learn
- Choose and write window frames that produce the exact rows you intend
- Solve gaps and islands, streak and sessionization problems in pure SQL
- Write recursive CTEs for hierarchies and generated sequences
- Use LATERAL joins and grouping sets to replace repeated queries
- Read EXPLAIN ANALYZE output and identify the costly step
- Design indexes and rewrites that make analytical queries faster
Course content
Window functions in depth
Recursive and advanced CTEs
- Video: Recursive CTEs explained step by step 12 min
- Video: Hierarchies: org charts and bills of materials 11 min
- Article: Generating sequences and date spines 9 min
- Video: Materialized CTEs and when PostgreSQL inlines them 8 min
Powerful query patterns
- Video: LATERAL joins for top N per group 11 min
- Article: GROUPING SETS, ROLLUP and CUBE 10 min
- Video: Funnels with strict step ordering 12 min
- Video: Pivoting and unpivoting 9 min
Reading query plans
- Article: How the planner chooses a plan 10 min
- Video: EXPLAIN ANALYZE line by line 13 min
- Video: Join algorithms: nested loop, hash and merge 10 min
Making queries faster
- Article: Indexes that help analytical queries 11 min
- Video: Rewrites that change everything 12 min
- Video: Partitioning large tables 10 min
- Article: Summary tables and materialized views 9 min
Putting it together
- Video: Case study: a subscription metrics query 13 min
- Video: Reviewing SQL written by others 9 min
- Article: Porting patterns across databases 9 min
- Quiz: Final check 6 min
Requirements
- Confident with joins, GROUP BY and CTEs
- Basic familiarity with window functions
- PostgreSQL or Docker installed
Description
You already write joins, aggregations and simple window functions. This course is about the problems that come next: sessions from raw click events, streaks and gaps, funnels with strict ordering, hierarchies of unknown depth, and the query that worked fine last year and now takes four minutes.
What we cover
- Window frames in detail, including ROWS, RANGE and GROUPS
- Gaps and islands, sessionization and deduplication patterns
- Recursive CTEs for hierarchies and date spines
- LATERAL joins, GROUPING SETS, ROLLUP and CUBE
- Reading EXPLAIN ANALYZE output and finding the real bottleneck
- Indexes that help analytical queries, and the ones that do not
Approach
Each pattern is introduced with a small table you can reason about by hand, then applied to a larger realistic dataset of web events and subscriptions. We use PostgreSQL 17 for the examples and highlight syntax that differs in BigQuery, Snowflake and DuckDB, such as QUALIFY.
The tuning section is practical rather than theoretical. You will take slow queries, read their plans, form a hypothesis, change one thing and measure again. That loop is the skill, and it works on any database.
Who this course is for
- Analysts and analytics engineers who write SQL daily
- Backend developers who own reporting queries
- Anyone preparing for advanced SQL interview rounds
Instructor
Aiko Tanaka
Analytics lead teaching SQL that answers business questions, from first SELECT to window functions
- 4.4 instructor rating
- 23 reviews
- 6,830 students
- 3 courses
- Sample
I have spent most of my career turning vague questions into queries: which customers came back, where the funnel leaks, why revenue looks different in two reports. SQL is the tool I reach for first, and I teach it the way analysts actually use it.
My courses use realistic datasets with messy edges: duplicates, missing values, time zones and late data. You will learn to check your own results, explain a query to a colleague and keep cloud warehouse costs under control.
I write every exercise so it runs on free tools, PostgreSQL, DuckDB or the BigQuery sandbox, so you never need a company account to practise.
Student reviews
Mariana C.
Sample Sessionization lesson is gold
We replaced a slow Python job with the SQL sessionization pattern from this course. It is faster and easier to review.
Leila M.
Sample Best explanation of frames
I never understood why my running totals repeated values on the same day. The RANGE versus ROWS lesson explained it perfectly.
Rachel D.
Sample Worth it for the rewrites lesson
Five slow queries, five fixes, each measured. That lesson alone sped up three of our dashboards.
Ben S.
Sample Strong tuning section
Reading plans from the bottom up with BUFFERS was very practical. A bit PostgreSQL focused, which is fine for me.
Hyun J.
Sample Advanced but approachable
The funnel lesson was hard and very good. I would have liked one more exercise on recursive CTEs.
Oscar N.
Sample Great content, heavy pace
Clearly expert material. Section five moved quickly and I needed to pause a lot, but everything was correct.
Keep exploring
All Analytics and SQL courses
More in Analytics and SQL
$59.99
Enroll now