SQL
Using Common Table Expressions (CTEs) for Multi-Step Queries
Simplify complex SQL queries and improve readability by breaking them into logical, named subqueries using Common Table Expressions. Ideal for multi-step data processing.
WITH RegionalSales AS (
SELECT
Region,
SUM(SalesAmount) AS TotalSales
FROM
Orders
GROUP BY
Region
),
TopRegions AS (
SELECT
Region,
TotalSales
FROM
RegionalSales
WHERE
TotalSales > 500000
)
SELECT
o.OrderID,
o.CustomerName,
o.OrderDate,
rs.TotalSales AS RegionTotalSales
FROM
Orders o
JOIN
TopRegions tr ON o.Region = tr.Region
JOIN
RegionalSales rs ON o.Region = rs.Region
ORDER BY
o.OrderDate DESC;
How it works: Common Table Expressions (CTEs) allow you to define a temporary, named result set that you can reference within a single SQL statement. This snippet demonstrates how to use `WITH` clauses to first calculate total sales per region (`RegionalSales`), then filter for top-performing regions (`TopRegions`), and finally join these intermediate results with the original `Orders` table to retrieve specific order details from those regions, enhancing query readability and modularity.