sql / intermediate
Snippet
Structuring Modular Logic Using Common Table Expressions
Common Table Expressions (CTEs) define temporary named result sets using the WITH clause. They improve query readability and allow breaking complex multi-step transformations into modular logic blocks within a single execution statement.
snippet.sql
sql
1
2
3
4
5
6
7
8
9
10
11
12
13
WITH regional_sales AS (SELECT region, SUM(amount) AS total_salesFROM salesGROUP BY region),top_regions AS (SELECT regionFROM regional_salesWHERE total_sales > 100000)SELECT r.region, s.sale_date, s.amountFROM sales sJOIN top_regions r ON s.region = r.region;
Breakdown
1
WITH regional_sales AS (
Begins the CTE definition block, assigning the temporary result set name 'regional_sales'.
2
SELECT region, SUM(amount) AS total_sales
Aggregates sales amounts per region within the first CTE block.
3
FROM sales
Queries the base sales table for the initial aggregation.
4
GROUP BY region
Groups individual sales transactions by region identifier.
5
),
Closes the first CTE definition and prepares for subsequent CTE definitions.
6
top_regions AS (
Defines a second CTE named 'top_regions' that consumes the first CTE.
7
SELECT region
Retrieves the region identifier from the previous CTE result.
8
FROM regional_sales
References the previously defined 'regional_sales' CTE as a data source.
9
WHERE total_sales > 100000
Filters aggregated total sales to include only high-performing regions.
10
)
Closes the second CTE block.
11
SELECT r.region, s.sale_date, s.amount
Main query selecting detailed transaction attributes.
12
FROM sales s
Sets the primary sales transaction table as the main source.
13
JOIN top_regions r ON s.region = r.region;
Joins the primary sales rows against the filtered CTE list of top regions.