SQL
Enhance SQL Readability with Common Table Expressions (CTEs)
Improve the organization and readability of complex SQL queries by using Common Table Expressions (CTEs). They define temporary, named result sets for clearer, modular SQL logic.
WITH RecentOrders AS (
SELECT order_id, customer_id, total_amount
FROM orders
WHERE order_date >= current_date - INTERVAL '30 days'
),
CustomerSpending AS (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM RecentOrders
GROUP BY customer_id
)
SELECT c.customer_name, cs.total_spent
FROM customers c
JOIN CustomerSpending cs ON c.customer_id = cs.customer_id
WHERE cs.total_spent > 500
ORDER BY cs.total_spent DESC;
How it works: Common Table Expressions (CTEs), defined with the `WITH` clause, allow you to create temporary, named result sets that you can reference within a single SQL statement. They greatly enhance query readability and modularity, especially for complex queries that involve multiple steps or hierarchical data. This example breaks down calculating recent customer spending into logical, understandable steps.