On January 21th at 14:15 UTC , progression through exercises will be unavailable for 10 minutes due to a planned maintenance break.
Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Nested CTEs


Excellent job! Another situation where nested CTEs can be useful is comparing two groups of items. Check it out:

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

total_salesman_earnings AS (
    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

FROM total_salesman_earnings s
GROUP BY group_name

We want to compare the average earnings of salespeople from Europe with those from other parts of the world.

In the first CTE, we define the groups: cities from Europe are labeled 'Europe', while cities outside Europe are labeled 'Other'. In the second CTE, we compute the total amount earned for each salesperson and combine this information with the group definition (i.e. 'Europe' or 'Other') for that salesperson.

In the outer query, we compute the group-level average.


Compare the average number of items sold by salespeople from the USA (country = 'USA') with those from all other countries.

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

Stuck? Here's a hint!

Modify the example query.