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

WITH average_per_customer AS (
  SELECT
    c.customer_id,
    AVG(total_amount) AS avg_order_value
  FROM customers c
  JOIN orders o
    ON c.customer_id = o.customer_id
  GROUP BY c.customer_id
)

SELECT AVG(avg_order_value) AS global_avg_order_value
FROM average_per_customer;

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 (average_per_customer in this case) which we can use later in the outer query. To learn more about Common Table Expressions, check out our Recursive Queries course.

A CTE is introduced before the main query in the following way: WITH cte_name 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 customer, it calculates the average order value for this customer.

After the closing parenthesis, 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.