Find the Average Order Value by Customer SQL Server

Find the Average Order Value by Customer Query Using MS SQL Server?

To find the average order value by customer, you need to:

1. Calculate the total value of each order.
2. Aggregate these order values by customer.
3. Calculate the average order value for each customer.

Assuming you have the following tables:

• OrderDetails:
  • OrderID
  • ProductID
  • Quantity
  • UnitPrice
• Orders:
  • OrderID
  • CustomerID
  • OrderDate

Here’s a SQL query to calculate the average order value by customer:

SQL Query

WITH OrderValues AS (

    SELECT

        o.CustomerID,

        o.OrderID,

        SUM(od.Quantity * od.UnitPrice) AS OrderValue

    FROM Orders o

    JOIN OrderDetails od ON o.OrderID = od.OrderID

    GROUP BY o.CustomerID, o.OrderID

),

AverageOrderValues AS (

    SELECT

        CustomerID,

        AVG(OrderValue) AS AverageOrderValue

    FROM OrderValues

    GROUP BY CustomerID

)

SELECT

    c.CustomerID,

    c.CustomerName,

    a.AverageOrderValue

FROM AverageOrderValues a

JOIN Customers c ON a.CustomerID = c.CustomerID

ORDER BY c.CustomerID;

Explanation:

1. CTE OrderValues:

   • SUM(od.Quantity * od.UnitPrice) AS OrderValue: Calculates the total value of each order.
   • GROUP BY o.CustomerID, o.OrderID: Groups by CustomerID and OrderID to compute the order value for each order.

2. CTE AverageOrderValues:

   • AVG(OrderValue) AS AverageOrderValue: Calculates the average order value for each customer.
   • GROUP BY CustomerID: Groups by CustomerID to aggregate the average order value for each customer.

3. Final Query:

   • JOIN Customers c ON a.CustomerID = c.CustomerID: Joins with the Customers table to get customer names.
   • ORDER BY c.CustomerID: Orders the results by CustomerID.

Example Data and Results

Assume the OrderDetails and Orders tables have the following data:

OrderDetails Table:

OrderID	ProductID	Quantity	UnitPrice
1	1	10	100
2	2	5	50
3	1	3	100
4	1	1	100
5	2	2	50

Orders Table:

OrderID	CustomerID	OrderDate
1	1001	2024-01-01
2	1001	2024-01-05
3	1002	2024-01-10
4	1002	2024-01-15
5	1003	2024-01-20

Customers Table:

CustomerID	CustomerName
1001	Alice
1002	Bob
1003	Charlie

Using the query, the result might be:

CustomerID	CustomerName	AverageOrderValue
1001	Alice	700
1002	Bob	400
1003	Charlie	100

This result shows the average value of orders placed by each customer.

You can execute this query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to get the average order value by customer. Adjust the table and column names according to your actual database schema.

https://mytecbooks.blogspot.com/2024/08/find-average-order-value-by-customer.html

Find the Average Order Value by Customer Query Using MS SQL Server programs interview questions and answers freshers experienced advanced level