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;
