Subqueries & CTEpracticeadvanced
CTEs Practice
Practice session: building and chaining CTEs, rewriting subqueries, recursive patterns.
Overview
Students practise writing CTEs to solve multi-step analytical problems. Tasks start with simple single-CTE rewrites and progress to chained CTEs and a recursive example.
Lesson Plan
| Time | Activity |
|---|---|
| 0–10 min | Recap: CTE syntax and chaining |
| 10–20 min | Walk through first task together |
| 20–60 min | Hands-on work: students solve tasks independently |
| 60–75 min | Review solutions, discuss readability improvements |
| 75–80 min | Wrap-up |
Key Concepts
- Naming intermediate results to avoid deeply nested subqueries
- Each CTE visible only within the same WITH block
- Debugging CTEs: run each CTE SELECT independently
- When a CTE improves clarity vs when a simple JOIN is better
- Recursive CTE anatomy: anchor member, recursive member, UNION ALL
✏️Practice
Tasks will be provided during the lesson.