SQL Topic
Common Table Expressions (CTEs)
A CTE (WITH clause) names a subquery so later parts of the query can reference it. CTEs make multi-step problems readable: dedupe first, number rows second, filter last.
When is Common Table Expressions (CTEs) used?
Multi-stage problems — gaps and islands, ranking before filtering, computing intermediate aggregates — where a single query would be unreadable.
Core syntax
WITH step1 AS (SELECT ...), step2 AS (SELECT ... FROM step1) SELECT ... FROM step2 — each step can reference earlier ones.Common mistakes
- Forgetting that CTEs are evaluated in order and cannot reference later ones.
- Using a CTE where a plain subquery is simpler.
- Not noticing that a CTE is scanned per reference (materialization is not guaranteed).
Practice Common Table Expressions (CTEs) questions
Work through them in order — each question builds on the last.