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 salesman_sold_items AS (
SUM(items_sold) AS total_items_sold
FROM daily_sales d
JOIN salesman s
ON d.salesman_id = s.id
GROUP BY salesman_id, city_id
city_average AS (
AVG(total_items_sold) AS city_avg
GROUP BY city_id
The first CTE,
salesman_sold_items computes the total number of items sold by each salesman. The second CTE,
city_average computes the city-level average. It's a very important performance metric for our company! Finally, the outer query computes the average overall.
Notice the computing the overall average per salesman would render a completely different result.