Introduction
Nested CTEs
11. Exercise 4
Summary

Instruction

Great! Now let's try a really challenging exercise.

Exercise

We define a 'Good' sales rep as one whose total amount earned is above the average total amount earned in their city.

We want to compare the average number of items sold between two groups of sales reps: the 'Good' reps and 'Bad' reps.

In the result, we want to get a table like this, but with appropriate numbers:

Label Average
Good 3.12
Bad 4.15

Stuck? Here's a hint!

In the first CTE, compute the total amount earned by each salesperson. Use the results of the first CTE to compute the city-level average in the second CTE.

In the third CTE, use the computed values to label each salesperson properly.

In the fourth CTE, compute the total number of items sold by each sales rep. In the outer query, compute the average number of items sold per group.

Yes, this query is loooong!