Common Table Expressions

A CTE is a named subquery written before the query that uses it. Same result as nesting, read top to bottom instead of inside out.

Once a query has two or three stages, this is the difference between something a colleague can follow and something they have to unpick.

The CTE computes the counts; the main query joins them to names. Each stage does one thing and can be read on its own.

A recursive CTE can walk a hierarchy, such as a category tree or a reply chain. It is genuinely useful and it has one rule: it must have a stopping condition.

Without one it runs forever. In this course that shows up as a timeout after eight seconds. Against a real production database it shows up as an incident.