Find the Most Expensive Product Purchased by Each Customer

Find the Most Expensive Product Purchased by Each Customer Query Using MS SQL Server?

To find the most expensive product purchased by each customer using Microsoft SQL Server, you need to:

1. Aggregate purchase data to determine the most expensive product for each customer.
2. Join with relevant tables to get detailed product and customer information.

Assuming you have the following tables:

• Orders table with columns: OrderID, CustomerID.
• OrderItems table with columns: OrderID, ProductID, Quantity, UnitPrice.
• Products table with columns: ProductID, ProductName.

Here's a step-by-step SQL query to achieve this:

SQL Query

-- Find the most expensive product purchased by each customer

WITH CustomerProductExpenses AS (

    SELECT

        o.CustomerID,

        oi.ProductID,

        p.ProductName,

        oi.UnitPrice * oi.Quantity AS TotalExpense

    FROM

        Orders o

        JOIN OrderItems oi ON o.OrderID = oi.OrderID

        JOIN Products p ON oi.ProductID = p.ProductID

),

MaxExpensePerCustomer AS (

    SELECT

        CustomerID,

        ProductID,

        ProductName,

        TotalExpense,

        ROW_NUMBER() OVER (

            PARTITION BY CustomerID

            ORDER BY TotalExpense DESC

        ) AS rn

    FROM

        CustomerProductExpenses

)

SELECT

    CustomerID,

    ProductID,

    ProductName,

    TotalExpense

FROM

    MaxExpensePerCustomer

WHERE

    rn = 1

ORDER BY

    CustomerID;

Explanation

1. CustomerProductExpenses CTE:

   • oi.UnitPrice * oi.Quantity AS TotalExpense: Calculates the total expense for each product purchased by each customer (i.e., TotalExpense).
   • JOIN clauses: Join Orders, OrderItems, and Products tables to gather necessary data.

2. MaxExpensePerCustomer CTE:

   • ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY TotalExpense DESC) AS rn: Ranks the products purchased by each customer based on total expense in descending order.
   • PARTITION BY CustomerID: Ensures that the ranking is done separately for each customer.

3. Main Query:

   • WHERE rn = 1: Filters to get only the most expensive product for each customer.
   • ORDER BY CustomerID: Orders the results by CustomerID for better readability.

Additional Considerations

• Handling Ties: If multiple products have the same highest expense for a customer and you want to list all such products, you may need to use a different method (e.g., RANK() function).
• Indexes: Indexes on CustomerID, ProductID, and OrderID can improve performance.
• Data Accuracy: Ensure that the OrderItems table accurately captures product prices and quantities.

Adjust column names and table structures based on your actual schema. This query helps you identify the most expensive product purchased by each customer efficiently.