SQL
Simplify Complex SQL Queries with Common Table Expressions (CTEs)
Discover how to use Common Table Expressions (CTEs) to break down complex SQL queries into more readable and manageable, temporary result sets for better organization.
WITH RecentOrders AS (
SELECT order_id, user_id, order_date, total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
),
HighValueCustomers AS (
SELECT user_id, SUM(total_amount) AS total_spent
FROM RecentOrders
GROUP BY user_id
HAVING SUM(total_amount) > 1000
)
SELECT u.username, h.total_spent
FROM HighValueCustomers h
JOIN users u ON h.user_id = u.id
ORDER BY h.total_spent DESC;
How it works: This example uses CTEs to simplify a multi-step query. `RecentOrders` first filters orders from the last 30 days. `HighValueCustomers` then aggregates spending from those recent orders. Finally, it joins with the `users` table to retrieve usernames for customers who spent over 1000 in the last 30 days, making the logic much clearer and modular.