SQL
Structuring Complex SQL Queries with Common Table Expressions (CTEs)
Improve SQL query readability and organize complex logic using Common Table Expressions (CTEs), ideal for multi-step data processing in web development.
-- Example: Find customers who made more than one order
-- and the total amount they spent.
WITH CustomerOrders AS (
SELECT
customer_id,
COUNT(order_id) AS total_orders,
SUM(amount) AS total_spent
FROM
orders
GROUP BY
customer_id
),
HighVolumeCustomers AS (
SELECT
customer_id,
total_orders,
total_spent
FROM
CustomerOrders
WHERE
total_orders > 1
)
SELECT
c.customer_id,
u.name AS customer_name,
hvc.total_orders,
hvc.total_spent
FROM
HighVolumeCustomers hvc
JOIN
customers u ON hvc.customer_id = u.id
ORDER BY
hvc.total_spent DESC;
-- Assume a 'customers' table exists with 'id' and 'name' columns.
-- Example 'customers' table:
-- +----+----------+
-- | id | name |
-- +----+----------+
-- | 101| Alice |
-- | 102| Bob |
-- | 103| Charlie |
-- +----+----------+
How it works: Common Table Expressions (CTEs), defined using the `WITH` clause, provide a way to write auxiliary statements for use within a larger query. They act like temporary, named result sets that you can reference within the main query or other CTEs, greatly improving the readability, modularity, and maintainability of complex SQL logic. This example chains two CTEs to first aggregate customer order data and then filter for high-volume customers before joining with customer details.