Find the Top 3 Products with the Highest Total Sales in the Last Year Query Using MS SQL Server?

To find the top 3 products with the highest total sales in the last year using Microsoft SQL Server, you will need to:

1. Filter sales data to include only the last year.
2. Aggregate total sales for each product.
3. Sort the results to find the products with the highest total sales.
4. Limit the results to the top 3 products.

Assuming you have the following tables:

• Sales table with columns: SaleDate, ProductID, SaleAmount.
• Products table with columns: ProductID, ProductName.

Here’s a step-by-step SQL query to find the top 3 products with the highest total sales in the last year:

SQL Query

-- Find the top 3 products with the highest total sales in the last year

WITH SalesLastYear AS (

    SELECT

        ProductID,

        SUM(SaleAmount) AS TotalSales

    FROM

        Sales

    WHERE

        SaleDate >= DATEADD(YEAR, -1, GETDATE()) -- Filter for the last year

    GROUP BY

        ProductID

),

TopProducts AS (

    SELECT

        sl.ProductID,

        p.ProductName,

        sl.TotalSales,

        ROW_NUMBER() OVER (ORDER BY sl.TotalSales DESC) AS rn

    FROM

        SalesLastYear sl

        JOIN Products p ON sl.ProductID = p.ProductID

)

SELECT

    ProductID,

    ProductName,

    TotalSales

FROM

    TopProducts

WHERE

    rn <= 3

ORDER BY

    TotalSales DESC;

Explanation

1. SalesLastYear CTE:

   • SUM(SaleAmount) AS TotalSales: Aggregates the total sales amount for each product.
   • WHERE SaleDate >= DATEADD(YEAR, -1, GETDATE()): Filters sales data to include only records from the last year. GETDATE() returns the current date and time, and DATEADD(YEAR, -1, GETDATE()) calculates the date one year ago.
   • GROUP BY ProductID: Groups the results by ProductID to compute total sales for each product.

2. TopProducts CTE:

   • ROW_NUMBER() OVER (ORDER BY sl.TotalSales DESC) AS rn: Assigns a row number to each product based on total sales, ordered in descending order. This is used to rank products by their sales.
   • JOIN Products p ON sl.ProductID = p.ProductID: Joins with the Products table to get product details such as ProductName.

3. Main Query:

   • WHERE rn <= 3: Filters to get only the top 3 products based on their total sales.
   • ORDER BY TotalSales DESC: Orders the results by total sales in descending order to ensure that the top products are listed first.

Additional Considerations

• Date Handling: Ensure that the SaleDate column in the Sales table is correctly populated with dates and that you are accurately filtering for the last year.
• Performance: Indexes on SaleDate, ProductID, and SaleAmount can help improve query performance, especially with large datasets.
• Data Quality: Verify that the Sales table contains accurate and complete sales data.

Adjust column names and table structures according to your actual schema. This query effectively identifies the top 3 products with the highest total sales over the past year.