Excellent! Nested CTEs are very handy when you want to compute complex statistics in one query. For example, you can use nested CTEs to compute averages of averages. Take a look:
WITH SalesmanSoldItems AS (
SUM(ItemsSold) AS TotalItemsSold
FROM DailySales d
INNER JOIN Salesman S
ON D.SalesmanId = S.Id
GROUP BY SalesmanId,
CityAverage AS (
AVG(TotalItemsSold) AS CityAvg
GROUP BY CityId
AVG(CityAvg) AS AvgCityAvg
The first CTE,
SalesmanSoldItems, computes the total number of items sold by each sales rep. The second CTE,
CityAverage, computes the city-level average. It's a very important performance metric for our company! Finally, the outer query computes the average overall.
Notice that computing the overall average per rep would render a completely different result.