Find Products with Sales that Increased Year-over-Year

Find Products with Sales that Increased Year-over-Year Query Using MS SQL Server?

To find products with sales that increased year-over-year using Microsoft SQL Server, you need to compare sales figures for each product between consecutive years. This typically involves:

1. Aggregating sales data by year for each product.
2. Comparing sales figures between consecutive years to identify products with an increase.

Assuming you have a Sales table with columns:

• ProductID: The identifier for each product.
• SaleDate: The date of the sale.
• SaleAmount: The amount of the sale.

Here’s a step-by-step approach to writing the query:

SQL Query

WITH AnnualSales AS (

    SELECT

        ProductID,

        YEAR(SaleDate) AS SaleYear,

        SUM(SaleAmount) AS TotalSales

    FROM

        Sales

    GROUP BY

        ProductID,

        YEAR(SaleDate)

),

SalesComparison AS (

    SELECT

        a.ProductID,

        a.SaleYear AS CurrentYear,

        a.TotalSales AS CurrentYearSales,

        b.SaleYear AS PreviousYear,

        b.TotalSales AS PreviousYearSales

    FROM

        AnnualSales a

        JOIN AnnualSales b ON a.ProductID = b.ProductID

        AND a.SaleYear = b.SaleYear + 1

)

SELECT

    ProductID,

    CurrentYear,

    CurrentYearSales,

    PreviousYear,

    PreviousYearSales

FROM

    SalesComparison

WHERE

    CurrentYearSales > PreviousYearSales

ORDER BY

    ProductID, CurrentYear;

Explanation

1. AnnualSales CTE:

   • Aggregates sales by year and product.
   • YEAR(SaleDate) AS SaleYear: Extracts the year from the sale date.
   • SUM(SaleAmount) AS TotalSales: Calculates the total sales amount for each product per year.
   • GROUP BY ProductID, YEAR(SaleDate): Groups results by product and year.

2. SalesComparison CTE:

   • Joins the AnnualSales CTE to itself to compare sales of the current year with the previous year.
   • a.SaleYear = b.SaleYear + 1: Ensures that the join matches the current year with the previous year.
   • Includes columns for both current year and previous year sales data.

3. Main Query:

   • Filters the results to show only those products where sales in the current year are greater than in the previous year.
   • WHERE CurrentYearSales > PreviousYearSales: Identifies the products with increased sales year-over-year.
   • ORDER BY ProductID, CurrentYear: Orders the results by product and year for clarity.

Additional Considerations

• Handling Missing Data: If there are gaps in data (e.g., missing sales records for certain years), ensure your logic accounts for this. The join condition assumes consecutive years, so non-consecutive years would not be compared.

• Performance: For large datasets, ensure the SaleDate, ProductID, and SaleAmount columns are indexed to optimize performance.

• Customizing Time Range: You can adjust the query to consider specific years or a range of years if needed.

Adjust the column names and table structure based on your actual schema. This query provides a clear method to identify products with increasing sales year-over-year.