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).