Alright, let's get started! We're going to tackle nested CTEs.
If you take a look into any database documentation, you will find out that you cannot put one CTE inside the parentheses of another CTE. Luckily, we can still use a simple technique to create nested CTEs. Take a look at the query:
WITH total_salesman AS (
s.id AS s_id,
c.id AS c_id,
SUM(distance) AS sum_kilometers
FROM salesman s
JOIN daily_sales ds
JOIN city c
GROUP BY s.id, c.id
max_salesman AS (
MAX(sum_kilometers) AS max_kilometers
GROUP BY c_id
The trick is that the second query,
max_salesman, uses the first CTE,
total_salesman, in the
FROM clause (
FROM total_salesman). That means that once we define the first CTE, we can freely use it in subsequent CTEs.
Now, what does that query do? In the first CTE, we find the total number of kilometers driven in each city by each salesman (on all days). In the second CTE, we take these sums and choose the salesman with the maximal distance in each city. Finally, in the outer query, we find the average maximal distance across all cities.
As you know, it's impossible to use one aggregate function inside another. Nested CTEs are a simple way to get rid of that restriction.