SQL
Enhance SQL Query Readability with Common Table Expressions (CTEs)
Improve the structure and readability of complex SQL queries by breaking them into logical, named sub-queries using non-recursive Common Table Expressions.
WITH RecentOrders AS (
SELECT
order_id,
customer_id,
total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
),
LargeCustomers AS (
SELECT
c.id AS customer_id,
c.name AS customer_name
FROM customers c
JOIN RecentOrders ro ON c.id = ro.customer_id
GROUP BY c.id, c.name
HAVING SUM(ro.total_amount) > 1000
)
SELECT
lc.customer_id,
lc.customer_name,
COUNT(ro.order_id) AS number_of_recent_orders,
SUM(ro.total_amount) AS total_recent_spent
FROM LargeCustomers lc
JOIN RecentOrders ro ON lc.customer_id = ro.customer_id
GROUP BY lc.customer_id, lc.customer_name
ORDER BY total_recent_spent DESC;
How it works: This query demonstrates the use of non-recursive Common Table Expressions (CTEs) to improve readability. It first defines `RecentOrders` for orders within the last 30 days, then `LargeCustomers` for those who spent over $1000 in that period. Finally, it joins these CTEs to retrieve combined information, making a complex query structured and easier to understand by breaking it into logical, named steps.