Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Nested CTEs
Summary

Instruction

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

Exercise

A salesperson performs well if their total amount earned is above the average amount earned in their city. We want to show which salespeople perform well.

For each salesperson, show their first_name, last_name, and a third column named label. This column will display either 'Above average' or 'Below average', based on the total amount earned by that person.

Stuck? Here's a hint!

In the first CTE, compute the total amount earned by each salesperson.

In the second CTE, compute the average total amount earned per city. Use the results computed in the first CTE.

Note that your first CTE has to select city_id from the daily_sales table so that you can use it in the second CTE.

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