Introduction
Nested CTEs
Summary

Instruction

Great! Let's try a bit more difficult exercise.

Exercise

A salesman performs well if his total amount earned is above average amount earned in their city. We want to show which salesmen perform well.

For each salesman show their first_name, last_name and the third column named label, with either 'Above average' or 'Below average', based on the total amount earned by the salesman.

Stuck? Here's a hint!

In the first CTE compute total amount earned by each salesman.

In the second CTE compute the average total amount earned per city. You have to use the results computed in the first CTE. Note that your first CTE has to select the salesman's city_id so that you can use it in the second CTE.

Finally, in the outer query combine the two CTEs to label salesmen properly. Use column city_id to join the first and the second CTE.