Advanced SQL — Windows, CTEs, Exec Plans
A deep-dive on Advanced SQL — part of a 36-topic evergreen learning series.
Why this session matters
Part of a 36-topic learning series on engineering, ML, and LLM systems. Each session is a 90-minute deep-dive on one topic — designed so anyone can pick it up cold. Every two topics are followed by a revision session with recall prompts and hands-on drills.
Session 02 · DE track 🗄️
SQL isn't just SELECT * — production data engineering leans hard on window functions, CTEs (including recursive), lateral joins, and reading execution plans. This session takes you from 'I can write joins' to 'I can debug why this query takes 40 seconds and fix it.' Every DE interview at scale will probe this.
Pre-read (30 min before session)
Watch 1–2 of these before the deep-dive:
- Advanced SQL Window Functions — Anthony DeBarros
- SQL Query Execution Plan — Database Star
- CTEs and Window Functions — Kris Jenkins
Then skim the Postgres Window Functions docs.
Deep-dive (90 min)
1. Window functions (35 min)
The most under-taught SQL feature. Covers ROW_NUMBER(), RANK(), DENSE_RANK(), LAG()/LEAD(), SUM/AVG OVER (PARTITION BY ... ORDER BY ...), and the crucial ROWS BETWEEN vs RANGE BETWEEN distinction. Real-world drills: rolling 7-day active users, running totals per customer, gaps-and-islands, top-N per group.
2. CTEs — regular & recursive (20 min)
Regular CTEs are just aliased subqueries (Postgres 12+ inlines them). Recursive CTEs unlock graph traversal in pure SQL — org charts, category trees, dependency chains. Anti-pattern: materialization surprises pre-PG12.
3. Reading execution plans (25 min)
EXPLAIN (ANALYZE, BUFFERS). Understanding Seq Scan vs Index Scan vs Bitmap Heap Scan. Nested Loop vs Hash Join vs Merge Join. Reading estimated-vs-actual row counts to spot bad statistics. Common fixes: adding indexes, ANALYZE after big changes, SET work_mem, materialized CTEs.
4. Query tuning patterns (10 min)
Kill correlated subqueries with joins. Kill NOT IN with NOT EXISTS (null semantics). Understand when partial/covering/expression indexes help. Watch out for implicit type casts breaking index use.
Reading list
- Markus Winand — SQL Performance Explained (short, essential)
- Postgres official docs: Window Functions, EXPLAIN
- Use The Index, Luke — https://use-the-index-luke.com/
Research links
Hands-on drill
Given a orders(user_id, order_date, amount) table, write a single query returning: for each user, their 7-day rolling sum of amount, ordered by user_id + date. Then EXPLAIN ANALYZE it and identify the bottleneck.
Post-session checklist
- Can you explain window functions with PARTITION BY in 60 seconds?
- Can you explain the difference between ROWS BETWEEN and RANGE BETWEEN in 60 seconds?
- Can you explain how to read an EXPLAIN ANALYZE output in 60 seconds?
- Did you complete the hands-on drill above?
- Did you write 3 flashcards for tomorrow's recall?
- What's the one thing you'd want to revisit in the next revision session?
What's next
Session 03 continues the series. See the hub page for the full sequence and revision pattern.