Introduction
Nested CTEs
7. Nested CTEs with subqueries
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: first, we count the number of sales reps who sold more than 5 items on a given day in a given city. Then, we find the average number of such reps across all cities for each day. Finally, we 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 CountGoodSalesmen AS (
  SELECT
    Day,
    Name,
    COUNT(DISTINCT S.Id) AS CountGood
  FROM Salesman S
  INNER JOIN DailySales DS
    ON S.Id = DS.SalesmanId
  INNER JOIN City C
    ON S.CityId = C.Id
  WHERE ItemsSold > 5
  GROUP BY Day,
    Name
),
AvgSalesmenDaily AS (
  SELECT
    Day,
    AVG(CountGood) AS AvgCount
  FROM CountGoodSalesmen
  GROUP BY Day
)

SELECT
  Day,
  AvgCount
FROM AvgSalesmenDaily
WHERE AvgCount = (
    SELECT
      MAX(AvgCount)
    FROM AvgSalesmenDaily
  );

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

Exercise

Your turn! First, find the total number of customers in each region on each day. Then, calculate the average sum 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 AvgRegionCustomers).

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.