Save up to $499! Grab all Python courses for $49 or all online courses we’ve ever launched for only $169. Only Feb 11-16. Happy Valentine's!
Introduction
Nested CTEs
Summary

Instruction

Excellent job. Another situation when nested CTEs can be useful is comparing two groups of items. Take a look:

WITH grouping AS (
  SELECT
    id AS city_id,
    CASE WHEN region = 'Europe' THEN region ELSE 'Other' END AS group_name
  FROM city
),

total_salesman_earnings AS (
  SELECT
    salesman_id,
    group_name,
    SUM(amount_earned) AS total_amount
  FROM daily_sales d
  JOIN salesman s
  ON d.salesman_id = s.id
  JOIN grouping g
  ON g.city_id = s.city_id
  GROUP BY salesman_id, group_name
) 

SELECT
  group_name,
  AVG(total_amount)
FROM total_salesman_earnings s
GROUP BY group_name

We want to compare average earnings of salesman from Europe and other parts of the world. In the first CTE we define the groups: cities from Europe are labeled 'Europe', cities outside Europe are labeled 'Other'. In the second CTE, we compute the total amount earned for each salesman and combine this information with group definition for the salesman. In the outer query we compute the group-level average.

Exercise

Compare the average number of items sold by salesman from USA (country = 'USA') and other countries in the world.

Name the group column group_name. In your query use values 'USA' and 'Other' to label the groups.

Stuck? Here's a hint!

You have to modify the example query.