CTEs
Name a subquery and reuse it like a temporary table.
Questions this answers
- →How do I make a complex query more readable?
- →How do I reuse a subquery without repeating it?
- →How do I break a multi-step transformation into stages?
How it works
A CTE (Common Table Expression) is a named subquery defined with WITH. It works like a temporary view that only exists for the duration of the query. CTEs make complex queries dramatically more readable — instead of nesting subqueries five levels deep, you define each step by name and reference it.
Basic CTE
WITH high_listeners AS ( SELECT name, genre, monthly_listeners FROM artists WHERE monthly_listeners > 5000000 ) SELECT genre, COUNT(*) AS count FROM high_listeners GROUP BY genre;
Multiple CTEs
WITH
pop_artists AS (
SELECT * FROM artists WHERE genre = 'Pop'
),
top_pop AS (
SELECT * FROM pop_artists
ORDER BY monthly_listeners DESC
LIMIT 10
)
SELECT name, monthly_listeners FROM top_pop;💡 Chain multiple CTEs with commas after the first WITH.