Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Customer Behavior Analysis in SQL Server

Introduction
Customer activity
Finding good customers
Summary

Instruction

Great! Now that we know the general average order value per customer (1636.622), we can use the value of 1636 to find good customers. Look at the query below:

SELECT
  C.CustomerID,
  AVG(TotalAmount) AS AvgOrderValue
FROM Customers C
JOIN Orders O
  ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID
HAVING AVG(TotalAmount) > 1636;

In the query, we simply showed each customer with their average order value. This time, however, we used a HAVING clause with the average value we calculated earlier. This will ensure that only customers above that threshold are included in the results.

Exercise

The average order value per customer in Italy is 1905.9063. Now, for each Italian customer with an average order value above that, show the following columns: CustomerID, FullName, and AvgOrderValue. Order the results by average order value, in descending order.

Stuck? Here's a hint!

You will need to include CustomerID and FullName columns in the GROUP BY clause.