It's Autumn! Prices fall like leaves! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Nested CTEs
Summary

Instruction

Okay, let's get started! We're going to tackle nested CTEs.

If you take a look into PostgreSQL's documentation, you''ll 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 following query:

WITH total_salesman AS (
  SELECT
    s.id AS s_id,
    c.id AS c_id,
    SUM(distance) AS sum_kilometers
  FROM salesman s 
  JOIN daily_sales ds 
    ON s.id = ds.salesman_id 
  JOIN city c 
    ON s.city_id = c.id
  GROUP BY s.id, c.id
),

max_salesman AS (
  SELECT
    c_id,
    MAX(sum_kilometers) AS max_kilometers
  FROM total_salesman
  GROUP BY c_id
)

SELECT
  AVG(max_kilometers)
FROM max_salesman;

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 this query do? In the first CTE, it finds the total number of kilometers driven in each city by each salesman (on all days). In the second CTE, it takes those sums and uses them to find the salesman with the maximal travel distance in each city. Finally, in the outer query, we find the average maximal distance across all cities.

As we learned, we can't put one aggregate function inside another. Nested CTEs are a simple way to get around that restriction.

Exercise

Now it's your turn! We will clearly state each step you need so you don't get confused.

First, find the daily sum of amount_earned in each city. Then, find the average daily amount for all cities for all days. Finally, show the id and name of each city and the number of daily sums that exceeded the average daily amount across all cities.

Stuck? Here's a hint!

SUM amounts earned in the first CTE, then find the average in the second CTE. In the outer query, use COUNT and make use of the results from both CTEs in the WHERE clause:

WHERE sum_amount > avg_amount