Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Nested CTEs
8. Exercise 1
Summary

## Instruction

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 (
SELECT
SalesmanId,
CityId,
SUM(ItemsSold) AS TotalItemsSold
FROM DailySales d
INNER JOIN Salesman S
ON D.SalesmanId = S.Id
GROUP BY SalesmanId,
CityId
),
CityAverage AS (
SELECT
CityId,
AVG(TotalItemsSold) AS CityAvg
FROM SalesmanSoldItems
GROUP BY CityId
)

SELECT
AVG(CityAvg) AS AvgCityAvg
FROM CityAverage


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.

## Exercise

Compute the city-level average of the total distance travelled by each salesperson. Then compute the company-level average. Name the column AvgCityAvg. Use the example provided in the explanation.

### Stuck? Here's a hint!

You can modify the example from the explanation.