To help you understand a good use case for a CTE, let's work with a query from earlier in this chapter that uses a derived table:

USE lahmansbaseballdb;
 SELECT ROUND(AVG(average_salary), 0) AS average_of_all_teams_salaries
FROM
 (SELECT AVG(salary) average_salary
 FROM salaries
 GROUP BY teamid) AS team_salary
WHERE team_salary.average_salary > 2000000;

The preceding query will be rewritten into the following CTE: 

USE lahmansbaseballdb;
WITH avgsalarycte 
AS
(SELECT AVG(salary) AS average_salary
FROM salaries
GROUP BY teamid)

SELECT ROUND(AVG(average_salary), 0) AS average_of_all_teams_salaries
FROM avgsalarycte
WHERE average_salary > 2000000;

The CTE query will give you the same results as the derived table query (2537765), but may be a little bit easier to read and understand, especially if you aren't the one who wrote the query or if you are coming back to the query a long while after writing it. Of course, you would comment your code so that anyone can understand what you are trying to do, but it's still nice to make your code as simple as possible. This really comes into play when your derived tables get way more complicated than this example query, but still, the same concept applies.