Outcomes
- • Use subqueries intentionally for scoped comparisons.
- • Break complex transformations into named CTE stages.
- • Make long queries easy to debug and explain to teams.
← All coursesStart course free →Open playground →
Intermediate4 lessons + 3 bonus
Subqueries & CTEs
Break hard questions into readable steps. Subqueries and WITH blocks let you compose multi-stage logic that would be impossible — or unreadable — in a single query.
Fast SQL learning loop
- • Read the prompt and schema before writing.
- • Run early and often to validate each clause.
- • Compare expected output shape, not just syntax validity.
Professional learning blueprint
Structure complex SQL into readable, production-quality logic blocks.
Real-world use
- • Layered business logic for finance or growth analysis.
- • Reusable query templates for analytics engineering.
- • Investigative deep-dives with iterative query refinement.
Fast study plan
- 1. Split your query into stages and validate each stage output.
- 2. Name CTEs by intent, not by generic labels.
- 3. Refactor one old long query into clear CTE blocks as practice.
Deep learning journey
Compositional SQL Arc
Decompose complex analysis into readable subqueries and CTE pipelines.
Subquery strategy
Use scalar and correlated subqueries for scoped comparisons.
- • Scalar subqueries
- • EXISTS patterns
- • Scoped benchmarks
CTE layering
Build multi-stage transformations with clear intent.
- • WITH clause structure
- • Step-wise validation
- • Reusable intermediate tables
Pipeline readability
Ship production-quality complex SQL that teammates can maintain.
- • Naming conventions
- • Stage comments
- • Final output contracts
Bonus lesson units
Additional lessons to master this track (3)
1. Recursive CTEs
Traverse tree and graph structures — org charts, category hierarchies, and path finding — with WITH RECURSIVE.
2. CTE materialization control
Force or suppress CTE materialization with AS MATERIALIZED and AS NOT MATERIALIZED for performance tuning.
3. CTE vs temp tables vs views
When to promote a CTE to a VIEW or temp table — trade-offs for reuse, performance, and maintainability.
Expanded practice
Mission exercises (10)
1. Above-average salary markers
easyUse subquery to flag employees above global average salary.
2. Department benchmark filter
mediumUse correlated subquery against department-level average.
3. Two-stage CTE pipeline
mediumBuild a CTE chain for cleaned base rows and final summary output.
4. Top-customer CTE
hardReturn top spenders using staged CTE logic from orders and items.
5. EXISTS eligibility check
mediumUse EXISTS to keep only rows with qualifying related records.
6. Nested query rewrite
hardRefactor nested subquery into cleaner CTE architecture.
7. CTE debugging drill
easyRun each CTE stage independently and validate intermediate row counts.
8. Derived table comparison
mediumCompare derived table approach with CTE approach for same business question.
9. Multi-rule analysis pipeline
hardCreate a 3+ stage analysis pipeline with explicit business-rule stages.
10. Reusable CTE view pattern
hardDesign a repeatable CTE template for recurring KPI requests.
Study references
What to study next
Some questions can't be answered in one query. Subqueries and CTEs let you compose multi-stage logic — each step building cleanly on the last — without losing readability or correctness.
What you'll learn
- Scalar subqueries in
SELECT(return a single value per row) INandNOT INsubqueries inWHEREEXISTSandNOT EXISTS— often faster thanINon large tables- Correlated subqueries — where the inner query references the outer row
WITH(Common Table Expressions) — name and reuse intermediate results- Chaining multiple CTEs to break a complex problem into named steps
What you'll practice on
The HR database and the Hospital database. You'll write queries like "employees who earn more than their department's average" and "patients who have never had a follow-up appointment."
Prerequisites
SQL Fundamentals + Filtering & Logic + Joins.