SQL
SQL Query: Structure Complex Logic with Common Table Expressions (CTEs)
Enhance SQL query readability and modularity by breaking down complex logic into manageable, reusable steps using Common Table Expressions (WITH clause).
WITH
RecentOrders AS (
SELECT
order_id, customer_id, order_date, total_amount
FROM
orders
WHERE
order_date >= CURRENT_DATE - INTERVAL '30 days'
),
CustomerTotals AS (
SELECT
customer_id,
COUNT(order_id) AS total_recent_orders,
SUM(total_amount) AS total_recent_spending
FROM
RecentOrders
GROUP BY
customer_id
)
SELECT
c.customer_id,
c.customer_name,
ct.total_recent_orders,
ct.total_recent_spending
FROM
customers c
JOIN
CustomerTotals ct ON c.customer_id = ct.customer_id
WHERE
ct.total_recent_spending > 1000
ORDER BY
ct.total_recent_spending DESC;
How it works: Common Table Expressions (CTEs), introduced by the `WITH` clause, allow you to define temporary, named result sets that you can reference within a single SQL statement. This snippet uses two CTEs: `RecentOrders` filters orders from the last 30 days, and `CustomerTotals` aggregates spending for those customers. The main query then joins these CTEs with the `customers` table to retrieve recent spending details, significantly improving query readability and maintainability for multi-step logic.