SQL
Simplify Complex SQL with Common Table Expressions (CTEs)
Learn to use SQL CTEs to break down complex queries into readable, reusable, and more manageable steps, improving query performance and clarity.
WITH RecentOrders AS (
SELECT
order_id,
customer_id,
order_date,
total_amount
FROM
orders
WHERE
order_date >= NOW() - INTERVAL '30 days'
),
CustomerAvgSpend AS (
SELECT
customer_id,
AVG(total_amount) AS average_spend
FROM
RecentOrders
GROUP BY
customer_id
)
SELECT
ro.order_id,
ro.customer_id,
ro.order_date,
ro.total_amount,
cas.average_spend
FROM
RecentOrders ro
JOIN
CustomerAvgSpend cas ON ro.customer_id = cas.customer_id
WHERE
ro.total_amount > cas.average_spend;
How it works: This snippet demonstrates using Common Table Expressions (CTEs) to simplify a multi-step query. `RecentOrders` first filters orders from the last 30 days. `CustomerAvgSpend` then calculates the average spending for these customers based on recent orders. Finally, the main query joins these CTEs to find recent orders where the total amount exceeds the customer's average spend, making the logic much clearer than nested subqueries.