SQL
Simplify Complex SQL Queries with CTEs
Improve SQL query readability and modularity by breaking down complex logic into manageable, named Common Table Expressions (CTEs) for better maintainability.
WITH RecentOrders AS (
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date >= DATE('now', '-30 days')
),
CustomerSpend AS (
SELECT customer_id, SUM(total_amount) AS thirty_day_spend
FROM RecentOrders
GROUP BY customer_id
)
SELECT c.customer_name, cs.thirty_day_spend
FROM customers c
JOIN CustomerSpend cs ON c.customer_id = cs.customer_id
WHERE cs.thirty_day_spend > 100
ORDER BY cs.thirty_day_spend DESC;
How it works: Common Table Expressions (CTEs), defined with the WITH clause, enhance readability by breaking down complex queries into logical, named sub-queries. This example first defines 'RecentOrders' and then 'CustomerSpend' based on it, making the final selection clearer and more maintainable, especially for multi-step data processing.