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.