Best April deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Nested CTEs
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 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 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.

## 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 from the explanation.