Common table expressions

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Name a query result for one following statement.

Run the example

WITH RegionCounts AS (
    SELECT Region, COUNT(*) AS CustomerCount
    FROM dbo.Customers GROUP BY Region
)
SELECT Region, CustomerCount
FROM RegionCounts
WHERE CustomerCount >= 5
ORDER BY Region;

Read it step by step

1. The CTE groups customers and names the intermediate result RegionCounts.
2. The following SELECT filters the grouped counts.
3. The CTE lasts for this one statement; it is not a stored table.

Check the result

The filter applies to the CTE result, after grouping.

Practice

Use a CTE to count customers per City, then keep cities with at least three.

WITH CityCounts AS (
    SELECT City, COUNT(*) AS CustomerCount
    FROM dbo.Customers GROUP BY City
)
SELECT City, CustomerCount
FROM CityCounts
WHERE CustomerCount >= 3
ORDER BY City;