Introduction
Nested CTEs
10. Exercise 3
Summary

Instruction

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

WITH Grouping AS (
  SELECT
    Id AS CityId,
    CASE WHEN Region = N'Europe' THEN Region ELSE N'Other' END AS GroupName
  FROM City
),
TotalSalesmanEarnings AS (
  SELECT
    SalesmanId,
    GroupName,
    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,
    GroupName
)

SELECT
  GroupName,
  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.

Exercise

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.