CTEs

A common table expression names a query result for the rest of one statement. Use it to make the stages of your reasoning visible.

WITH and named steps

Write WITH name AS (SELECT ...), then the main statement. The main query can refer to that name like a table. A CTE does not create a permanent table or view.

Several CTEs

Separate CTE definitions with commas after one WITH keyword. Later CTEs can use earlier ones. For example, first summarize customer orders, then filter that summary.

Aggregate, then aggregate again

You cannot generally write AVG(COUNT(*)) at the same query level. Calculate a count per customer in one step, then average those counts in an outer query.

Scope and performance

The name exists for this statement only. A CTE makes logic readable, but is not a universal performance improvement. A database may inline it, materialize it, or apply other optimizations.