Good job! In the previous report, we could see the average order value per customer. We're looking for good customers, so we want to know the general average order values, aggregated over customers. Here's the query:

WITH AveragePerCustomer AS (
  SELECT
    C.CustomerID,
    AVG(TotalAmount) AS AvgOrderValue
  FROM Customers C
  JOIN Orders O
    ON C.CustomerID = O.CustomerID
  GROUP BY C.CustomerID
)

SELECT AVG(AvgOrderValue) AS GlobalAvgOrderValue
FROM AveragePerCustomer;

In the query above, we use a concept known as a CTE (Common Table Expression). It works like a temporary table and has its own name (AveragePerCustomer in this case) which we can use later in the outer query. To learn more about Common Table Expressions, check out our Recursive Queries in MS SQL Server course.

A CTE is introduced before the main query in the following way: WITH CteName AS (). Inside the parentheses, we write the query we need. Remember to provide column aliases with the keyword AS so that you can refer to them in the outer query. In this case, the inner query is very similar to our previous report: for each checkout process, it calculates the process duration as the time difference between the first and last event.

After the closing bracket, we write our outer query. Note that we put the CTE's name in the FROM clause. In this case, we simply calculate the average order value based on all customer-level averages calculated in the CTE.

The result of the query is 1636.622.