SQL
Organize Complex Queries with Common Table Expressions (CTEs)
Improve SQL query readability and structure complex logic by breaking down queries into logical, named temporary result sets using CTEs (WITH clause).
WITH recent_users AS (
SELECT user_id, username, email
FROM users
WHERE last_login >= NOW() - INTERVAL '30 days'
),
active_posts AS (
SELECT post_id, title, user_id
FROM posts
WHERE status = 'published' AND created_at >= NOW() - INTERVAL '90 days'
)
SELECT ru.username, ru.email, ap.title AS recent_post_title
FROM recent_users ru
JOIN active_posts ap ON ru.user_id = ap.user_id
ORDER BY ru.username, ap.title;
How it works: This snippet uses Common Table Expressions (CTEs) to structure a more complex query. `recent_users` and `active_posts` are temporary, named result sets that make the query more readable and manageable. The final `SELECT` statement then joins these CTEs to retrieve the desired information, such as active posts by recently logged-in users. This approach enhances modularity and understanding of complex SQL logic.