Problem

Given a table of data with customer information, write a SQL query to find the top 10 customers with the highest average order value.
Example input:
CustomerID | Name | OrderValue
1 | John | 10
2 | Smith | 20
3 | Jane | 30
4 | Bill | 40
5 | Karen | 50
6 | Steve | 60
7 | Rachel | 70
8 | Mike | 80
9 | Tina | 90
10 | Jake | 100
Output:
CustomerID | Name | AverageOrderValue
6 | Steve | 60
7 | Rachel | 70
8 | Mike | 80
9 | Tina | 90
10 | Jake | 100

Solution

SELECT CustomerID, Name, AVG(OrderValue) AS AverageOrderValue
FROM Customers
GROUP BY CustomerID, Name
ORDER BY AverageOrderValue DESC
LIMIT 10;

/*

The solution is optimal because it uses the GROUP BY and ORDER BY clauses to group the customers by their ID and name, and then order them by their average order value.

*/