Save up to $499! Grab all Python courses for $49 or all online courses we’ve ever launched for only $169. Only Feb 11-16. Happy Valentine's!
Nested CTEs


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 =
  GROUP BY salesman_id, city_id

city_average AS (
    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.


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.