A common table expression (CTE) names a temporary query result inside a SQL statement. It separates a repeated or intermediate computation from the outer query, making analytical SQL easier to read and verify. section 1.7.3 section 10.1.5
Use a CTE when you want to:
- Name a calculated relation and query it from an outer SELECT. section 10.1.5
- Build an analytical statement incrementally and inspect intermediate results for correctness. section 1.7.3
- Keep multi-step analysis concise instead of moving every computation into imperative code. section 4.0
Calculate and render reputation rates
WITH top_users AS ( SELECT DisplayName, Reputation, round(reputation / day(today() - CreationDate)) AS rate, day(today() - CreationDate) AS days, CreationDate FROM users WHERE Reputation > 1_000_000 ) SELECT DisplayName, Reputation, rate, bar(rate, 150, 300, 35) AS bar FROM top_users ORDER BY rate DESC; The CTE prepares filtered users and their reputation rates; the outer query adds a bar representation and orders the final result.
Figure 1.3 Using DuckDB in a data pipeline
Use the pipeline diagram to place CTE-based SQL between data preparation and result consumption. section 1.6
