It's Autumn! Prices fall like leaves! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Nested CTEs
Summary

Instruction

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 (
  SELECT
    day,
    name,
    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 (
  SELECT
    day,
    AVG(count_good) as avg_count
  FROM count_good_salesmen
  GROUP BY day
)

SELECT
  day,
  avg_count
FROM avg_salesmen_daily
WHERE avg_count = (
  SELECT
    MAX(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).

Exercise

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.