Instruction
Great! Now let's try a really challenging exercise.
Exercise
We define 'Good' salesman as those whose total amount earned is above average amount earned in their city.
We want to compare the average number of items sold between the two groups of salesmen: the 'Good' salesmen and 'Bad' salesmen.
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 total amount earned by each salesman. Use results of the first CTE to compute the city-level average in the second CTE.
In the third CTE use the computes values to label salesman properly.
In the fourth CTE, compute total number of items sold by each salesman. Finally, in the outer query compute the average number of items sold per group.
Yes, this query is loooong!
