Snippet

Structuring Complex Multi-Step Queries using CTEs

Common Table Expressions (CTEs) define temporary named result sets that exist only for the scope of a single query. They break complex subqueries into modular, readable steps and allow subsequent CTEs to reference earlier ones.

snippet.sql

sql

WITH RegionalSales AS (
    SELECT
        region_id,
        SUM(total_amount) AS total_sales
    FROM sales_orders
    GROUP BY region_id
),
AverageSales AS (
    SELECT AVG(total_sales) AS avg_regional_sales
    FROM RegionalSales
)
SELECT
    r.region_id,
    r.total_sales
FROM RegionalSales r, AverageSales a
WHERE r.total_sales > a.avg_regional_sales;

Breakdown

WITH RegionalSales AS (

Declares the first CTE named RegionalSales to compute total sales per region.

GROUP BY region_id

Aggregates revenue figures for each individual region.

AverageSales AS (

Defines a second CTE that references the output of the first CTE to compute the global average.

WHERE r.total_sales > a.avg_regional_sales;

Filters for regions whose total sales exceed the calculated average across all regions.

Previous snippet Next snippet

More Intermediate SQL exercises →

Structuring Complex Multi-Step Queries using CTEs

Related