Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Nested CTEs
Summary

Instruction

Excellent. 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 want to find the maximal average. Well, nothing new so far. But what if we want to show the maximal average and the day the maximal average corresponds to? 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 with the lowest average across all regions (that means, show the day and the avg_region_customers).

Stuck? Here's a hint!

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