On January 21th at 14:15 UTC , progression through exercises will be unavailable for 10 minutes due to a planned maintenance break.
Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Nested CTEs


Good work! We will now add a small piece to our queries: a subquery. Why would we do that? Let's say we have the following task: we first count the number of salesmen who sold more than 5 items on a given day in a given city. Then, we find the average number of such salesmen across all cities for each day. Finally, we find the maximal average sales for that day.

Well, nothing new so far. But what if we want to show the maximal average sales and the day to which that average corresponds? Take a look:

WITH count_good_salesmen AS (
    COUNT(DISTINCT s.id) AS count_good 
  FROM salesman s 
  JOIN daily_sales ds 
    ON s.id=ds.salesman_id 
  JOIN city c 
    ON s.city_id=c.id 
  WHERE items_sold > 5 
  GROUP BY day, name

avg_salesmen_daily AS (
    AVG(count_good) as avg_count
  FROM count_good_salesmen
  GROUP BY day

FROM avg_salesmen_daily
WHERE avg_count = (
  FROM avg_salesmen_daily);

Look how we used a small subquery in the WHERE clause of the outer query to find the MAX(avg_count). Once we had the right row, we could select both columns (day and avg_count).


Your turn! First, find the total number of customers in each region on each day. Then, calculate the average number of customers across all regions on each day.

Finally, show the day and the avg_region_customers for the region with the lowest average across all regions.

Stuck? Here's a hint!

Use the SUM function in the first CTE and the average function in the second CTE. In the outer query, use the MIN function in a small subquery.