CTEs (Common Table Expressions) make complex queries readable.
CTE syntax:
WITH active_users AS (
SELECT user_id FROM events
WHERE event_date > '2024-01-01'
)
SELECT * FROM active_users
When to use CTEs:
- Multiple references to same subquery
- Breaking complex logic into steps
- Recursive queries
Recursive CTEs: Used for hierarchical data (org charts, category trees). Know the pattern even if you don't use it daily.