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.