Maven Analytics on YouTube · Open on YouTube
Core skills: SQL depth, statistics, Python and dashboards · Advanced beginner · Lesson 16 of 99 · 13 min video, then about 6 hours of practice
Breaking queries into steps with subqueries and CTEs
After this you can decompose a multi-step question into readable CTEs, use correlated and uncorrelated subqueries, and explain when each is appropriate.
Why it matters
Real analyst questions take several steps: find each customer's first order, then compare later orders to it, then summarise by cohort. CTEs (WITH clauses) let you write each step as a named, testable block. Prefer CTEs over deeply nested subqueries for readability, and run each CTE on its own while building so you can check its row count. The trap is one giant query you cannot debug.
How to use this resource
A clear comparison of CTEs, subqueries and temp tables, including recursive CTEs, in thirteen minutes.