Great! Now let's try a really challenging exercise.
We define 'Good' salespeople as those whose total amount earned is above the average amount earned in their city.
We want to compare the average number of items sold between two groups: the 'Good' salespeople and the 'Bad' salespeople.
In the result, we want to see a table like this, but with the appropriate numbers:
| label | average |
|---|---|
| Good | 3.12 |
| Bad | 4.15 |
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 properly label the salespeople.
In the fourth CTE, compute the total number of items sold by each salesperson. Finally, in the outer query, compute the average number of items sold per group.
Yes, this query is loooong!
