Find the Top 5 Customers by Total Purchase Amount

Find the Top 5 Customers by Total Purchase Amount Query Using MS SQL Server?

To find the top 5 customers by total purchase amount using Microsoft SQL Server, you need to:

1. Aggregate the total purchase amount for each customer.
2. Sort the customers by the total amount in descending order.
3. Limit the result to the top 5 customers.

Assuming you have the following tables:

• Orders table with columns OrderID, CustomerID, and OrderAmount.

Here’s a step-by-step SQL query to retrieve the top 5 customers by total purchase amount:

SQL Query

-- Find the top 5 customers by total purchase amount

WITH CustomerTotalPurchases AS (

    SELECT

        CustomerID,

        SUM(OrderAmount) AS TotalPurchaseAmount

    FROM

        Orders

    GROUP BY

        CustomerID

)

SELECT TOP 5

    c.CustomerID,

    c.CustomerName, -- Assuming you have a Customers table with a CustomerName column

    ctp.TotalPurchaseAmount

FROM

    CustomerTotalPurchases ctp

    JOIN Customers c ON ctp.CustomerID = c.CustomerID -- Optional: Join with Customers table to get customer details

ORDER BY

    ctp.TotalPurchaseAmount DESC;

Explanation

1. CustomerTotalPurchases CTE:

   • SUM(OrderAmount) AS TotalPurchaseAmount: Aggregates the total purchase amount for each customer.
   • GROUP BY CustomerID: Groups the results by CustomerID to get the total amount per customer.

2. Main Query:

   • SELECT TOP 5: Retrieves only the top 5 customers with the highest total purchase amounts.
   • JOIN Customers c ON ctp.CustomerID = c.CustomerID: Optionally join with the Customers table to get additional customer details such as CustomerName. Adjust or omit this join if your schema is different.
   • ORDER BY ctp.TotalPurchaseAmount DESC: Orders the results by total purchase amount in descending order so that the customers with the highest totals are listed first.

Additional Considerations

• Indexes: Ensure that CustomerID and OrderAmount columns are indexed for optimal performance, especially if dealing with a large dataset.

• Customer Details: If you don’t have a Customers table or similar, adjust the query to omit the join or adapt to your schema.

• Handling Ties: If multiple customers have the same total purchase amount and you need to handle ties in a specific way (e.g., selecting all with the same top amount), you might need additional logic. However, for simple top-N queries, the TOP clause suffices.

Adjust the column names and table structure according to your actual database schema. This query helps you identify the top 5 customers based on their total purchase amount.

https://mytecbooks.blogspot.com/2024/08/find-top-5-customers-by-total-purchase.html

Find the Top 5 Customers by Total Purchase Amount Query Using MS SQL Server programs interview questions and answers freshers experienced advanced