Find the Product with the Highest Sales in Each Region

Find the Product with the Highest Sales in Each Region Query Using MS SQL Server?

To find the product with the highest sales in each region using Microsoft SQL Server, you'll need to:

1. Aggregate sales data by region and product to determine total sales.
2. Identify the product with the highest total sales for each region.

Assuming you have the following tables:

• Sales table with columns: SaleID, ProductID, RegionID, SaleAmount.
• Products table with columns: ProductID, ProductName.
• Regions table with columns: RegionID, RegionName.

Here’s a step-by-step SQL query to find the product with the highest sales in each region:

SQL Query

-- Find the product with the highest sales in each region

WITH RegionProductSales AS (

    SELECT

        s.RegionID,

        s.ProductID,

        p.ProductName,

        SUM(s.SaleAmount) AS TotalSales

    FROM

        Sales s

        JOIN Products p ON s.ProductID = p.ProductID

    GROUP BY

        s.RegionID,

        s.ProductID,

        p.ProductName

),

RankedSales AS (

    SELECT

        rps.RegionID,

        rps.ProductID,

        rps.ProductName,

        rps.TotalSales,

        ROW_NUMBER() OVER (

            PARTITION BY rps.RegionID

            ORDER BY rps.TotalSales DESC

        ) AS rn

    FROM

        RegionProductSales rps

)

SELECT

    r.RegionName,

    rs.ProductID,

    rs.ProductName,

    rs.TotalSales

FROM

    RankedSales rs

    JOIN Regions r ON rs.RegionID = r.RegionID

WHERE

    rs.rn = 1

ORDER BY

    r.RegionName;

Explanation

1. RegionProductSales CTE:

   • SUM(s.SaleAmount) AS TotalSales: Aggregates the total sales amount for each product in each region.
   • GROUP BY s.RegionID, s.ProductID, p.ProductName: Groups the results by region and product to calculate the total sales for each product in each region.

2. RankedSales CTE:

   • ROW_NUMBER() OVER (PARTITION BY rps.RegionID ORDER BY rps.TotalSales DESC) AS rn: Ranks products within each region based on total sales in descending order. The product with the highest sales gets rank 1.
   • PARTITION BY rps.RegionID: Ensures the ranking is done separately for each region.

3. Main Query:

   • JOIN Regions r ON rs.RegionID = r.RegionID: Joins with the Regions table to get the region names.
   • WHERE rs.rn = 1: Filters to get only the product with the highest sales for each region.
   • ORDER BY r.RegionName: Orders the results by region name for better readability.

Additional Considerations

• Data Accuracy: Ensure that the Sales table correctly reflects all sales and that ProductID and RegionID are accurately assigned.
• Handling Ties: If multiple products have the same highest sales in a region and you want to handle such ties differently, consider using the RANK() function instead of ROW_NUMBER().
• Indexes: Indexes on RegionID, ProductID, and SaleAmount can help improve query performance.

Adjust column names and table structures based on your actual schema. This query will effectively identify the product with the highest sales in each region.