Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Basic multi-level aggregation
Multi-level aggregation in groups
Multi-level aggregation with custom classification
Three or more aggregation levels
13. Three aggregation levels – example 1
Summary

Instruction

Very well done!

Sometimes, we need more than two aggregation levels. For instance, we could calculate the average order value for each customer and then find the maximum average. Take a look at the query below:

WITH OrderValues AS (
  SELECT
    CustomerID,
    SUM(UnitPrice * Quantity) AS TotalPrice
  FROM Orders O
  JOIN OrderItems OI
    ON O.OrderID = OI.OrderID
  GROUP BY CustomerID
),

CustomerAverages AS (
  SELECT
    CustomerID,
    AVG(TotalPrice) AS AvgTotalPrice
  FROM OrderValues
  GROUP BY CustomerID
)

SELECT
  MAX(AvgTotalPrice) AS MaximalAverage 
FROM CustomerAverages;

Notice that we introduced two CTEs this time. There is no WITH keyword before the second CTE; we simply need a comma to separate the two.

In the first CTE, we calculate the value of each order and select it alongside CustomerID. In the second CTE, we compute the average order value for each customer based on the first CTE. Finally, in the outer query, we find the maximum average value from the second CTE.

Exercise

For each employee, calculate the average order value (after discount) and then show the minimum average (name the column MinimalAverage) and the maximum average (name the column MaximalAverage) values.

Hint: Use two CTEs. In the first CTE, select the EmployeeID and the total price after discount for each order. In the second CTE, calculate the average order value after discount for each employee. Finally, in the outer query, use the MIN() and MAX() functions.

Stuck? Here's a hint!

Use the following template:

WITH OrderValues AS (
  ...
),
CustomerAverages AS (
  ...
)
SELECT
  MIN(...) AS MinimalAverage,
  MAX(...) AS MaximalAverage
FROM CustomerAverages;