Combining Multiple CTEs

This lesson covers combining multiple CTEs together, so multi-stage aggregation stays readable. It's for anyone searching "SQL multiple CTE tutorial."

Separating definitions with commas — WITH name1 AS (...), name2 AS (...) — lets you define several CTEs (common table expressions) at once, splitting a complex multi-step aggregation into readable pieces.

The example computes each class's average score in class_avg, then uses that result inside above_avg to find students scoring above their class average. The key point: the first CTE's result can be referenced directly inside the second one.

A common early mistake is writing CTEs without thinking through their dependency order — a later CTE can reference an earlier one, but not the other way around, so it helps to think of it as "stacking blocks" from the top down. Being able to name each step of a multi-stage process — "first compute the class average, then find who beats it" — and lay them out in order dramatically improves how easy the query is to understand later.