Great! Let's try an exercise that's a bit more difficult.
A sales person performs well if their total amount earned is above the average total amount earned in their city. We want to show which sales reps perform well.
For each rep show their FirstName, LastName, and a column named Label, which will contain either N'Above average' or N'Below average', based on the total amount earned by that person.
In the first CTE, compute the total amount earned by each salesperson.
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 salesperson's CityId so that you can use it in the second CTE.
In the outer query, combine the two CTEs to label reps properly. Use the column CityId to join the first and second CTEs.
