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

## Instruction

Well done! Nested CTEs are very handy when you want to compute complex statistics in one query. For example, you can use nested CTEs to compute the average of some averages. Look at the query below:

WITH salesman_sold_items AS (
SELECT
salesman_id,
city_id,
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 (
SELECT
city_id,
AVG(total_items_sold) AS city_avg
FROM salesman_sold_items
GROUP BY city_id
)

SELECT AVG(city_avg)
FROM city_average


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. Finally, the outer query computes the overall average value for all city averages.

Notice that computing the overall average per salesman would render a completely different result.

## Exercise

Compute the city-level average of the total distance travelled by each salesman. Then, compute the company-level average. Use the example provided in the explanation.

### Stuck? Here's a hint!

You can modify the example in the explanation.