Kickstart 2020 with new opportunities! - 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.