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


Excellent job! Another situation where nested CTEs can be useful is when comparing two groups of items. Take a look:

WITH Grouping AS (
    Id AS CityId,
    CASE WHEN Region = N'Europe' THEN Region ELSE N'Other' END AS GroupName
  FROM City
TotalSalesmanEarnings AS (
    SUM(AmountEarned) AS TotalAmount
  FROM DailySales DS
  INNER JOIN Salesman S
    ON DS.SalesmanId = S.Id
  INNER JOIN Grouping G
    ON G.CityId = S.CityId
  GROUP BY SalesmanId,

  AVG(TotalAmount) AS AvgTotalAmount
FROM TotalSalesmanEarnings S
GROUP BY GroupName

We want to compare average earnings of sales reps from Europe with reps from other parts of the world. In the first CTE, we define the groups: cities within Europe are labeled N'Europe' and cities outside Europe are labeled N'Other'. In the second CTE, we compute the total amount earned for each salesperson and combine this information with the group definition for that person. In the outer query, we compute the group-level average.


Compare the average number of items sold by sales reps from the USA (Country = N'USA') with reps from other countries in the world.

Name the group column GroupName. In your query, use the values N'USA' and N'Other' to label the groups. The other column should be called Avg.

Stuck? Here's a hint!

Modify the example query.