Snippet

Structuring Multi-Step Logic Using Common Table Expressions

Common Table Expressions (CTEs) define temporary named result sets using the WITH clause. CTEs simplify complex database logic by breaking queries into modular, readable steps.

snippet.sql

sql

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
),
top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > 100000
)
SELECT o.order_id, o.region, o.amount
FROM orders o
JOIN top_regions tr ON o.region = tr.region;

Breakdown

WITH regional_sales AS (

Declares the first Common Table Expression to aggregate sales per region.

SELECT region, SUM(amount) AS total_sales

Calculates total sales amount grouped by region.

top_regions AS (

Declares a second CTE that filters regions based on the first CTE results.

WHERE total_sales > 100000

Applies a threshold condition to retain high-volume regions.

JOIN top_regions tr ON o.region = tr.region;

Joins the primary table with the CTE result set to filter qualifying orders.

Previous snippet Next snippet

More Intermediate SQL exercises →

Structuring Multi-Step Logic Using Common Table Expressions

Related