SQL
Structure Complex Queries with Common Table Expressions (CTEs)
Enhance SQL query readability and simplify complex logic by breaking down intricate operations into more manageable, named subquery blocks using CTEs.
WITH RecentOrders AS (
SELECT order_id, customer_id, order_total
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
),
HighValueCustomers AS (
SELECT customer_id
FROM customers
WHERE total_spent > 1000
)
SELECT ro.order_id, ro.order_total, c.customer_name
FROM RecentOrders ro
JOIN HighValueCustomers hvc ON ro.customer_id = hvc.customer_id
JOIN customers c ON hvc.customer_id = c.customer_id
ORDER BY ro.order_total DESC;
How it works: This query uses Common Table Expressions (CTEs) to improve readability. `RecentOrders` selects orders from the last 30 days, and `HighValueCustomers` identifies customers with high total spending. These CTEs are then joined with the `customers` table in the final `SELECT` statement, making a complex query more modular and easier to understand by defining logical steps.