SQL
Simplify Complex Queries with Common Table Expressions (CTEs)
Understand how to use CTEs (WITH clause) in SQL to break down complex queries into more readable and manageable, temporary result sets.
WITH RecentOrders AS (
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date >= '2023-01-01'
),
HighValueCustomers AS (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM RecentOrders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000
)
SELECT rc.order_id, rc.customer_id, hvc.total_spent
FROM RecentOrders rc
JOIN HighValueCustomers hvc ON rc.customer_id = hvc.customer_id
ORDER BY hvc.total_spent DESC;
How it works: This snippet uses two CTEs (`RecentOrders` and `HighValueCustomers`) to first filter orders by date and then identify high-value customers from those recent orders. Finally, it joins these CTEs to retrieve specific order details for high-value customers. CTEs enhance readability and modularity of complex queries.