Calculate Year-over-Year Growth for Each Product Query Using MS SQL Server?

To calculate the year-over-year (YoY) growth for each product, you need to follow these steps:

1. Calculate the total sales for each product for each year.
2. Calculate the sales for the previous year for comparison.
3. Compute the YoY growth percentage based on the difference between the current year and the previous year’s sales.

Assuming you have the following tables:

• OrderDetails:
  • OrderID
  • ProductID
  • Quantity
  • UnitPrice (price per unit of the product)
• Orders:
  • OrderID
  • OrderDate

Here’s a SQL query to calculate the year-over-year growth for each product:

SQL Query

WITH YearlySales AS (

    SELECT

        p.ProductID,

        YEAR(o.OrderDate) AS SalesYear,

        SUM(od.Quantity * od.UnitPrice) AS TotalSales

    FROM OrderDetails od

    JOIN Orders o ON od.OrderID = o.OrderID

    JOIN Products p ON od.ProductID = p.ProductID

    GROUP BY p.ProductID, YEAR(o.OrderDate)

),

SalesWithPreviousYear AS (

    SELECT

        ys.ProductID,

        ys.SalesYear,

        ys.TotalSales,

        LAG(ys.TotalSales) OVER (PARTITION BY ys.ProductID ORDER BY ys.SalesYear) AS PreviousYearSales

    FROM YearlySales ys

)

SELECT

    ProductID,

    SalesYear,

    TotalSales,

    PreviousYearSales,

    CASE 

        WHEN PreviousYearSales IS NULL THEN NULL

        ELSE ((TotalSales - PreviousYearSales) * 100.0 / PreviousYearSales)

    END AS YoYGrowthPercentage

FROM SalesWithPreviousYear

ORDER BY ProductID, SalesYear;

Explanation:

1. CTE YearlySales:

   • YEAR(o.OrderDate) AS SalesYear: Extracts the year from the OrderDate.
   • SUM(od.Quantity * od.UnitPrice) AS TotalSales: Calculates the total sales for each product in each year.
   • GROUP BY p.ProductID, YEAR(o.OrderDate): Groups the results by ProductID and year to get yearly sales totals.

2. CTE SalesWithPreviousYear:

   • LAG(ys.TotalSales) OVER (PARTITION BY ys.ProductID ORDER BY ys.SalesYear) AS PreviousYearSales: Uses the LAG() window function to get the total sales of the previous year for each product.

3. Final Query:

   • CASE WHEN PreviousYearSales IS NULL THEN NULL ELSE ((TotalSales - PreviousYearSales) * 100.0 / PreviousYearSales) END AS YoYGrowthPercentage: Computes the YoY growth percentage. If there is no data for the previous year (i.e., for the first year of data), it returns NULL.

Example Data and Results

Assume you have the following data:

OrderDetails Table:

OrderID	ProductID	Quantity	UnitPrice
1	1	10	100
2	1	5	100
3	2	20	200
4	1	15	100
5	2	10	200

Orders Table:

OrderID	OrderDate
1	2023-05-01
2	2023-07-15
3	2022-11-01
4	2023-12-01
5	2023-01-01

YearlySales CTE Result:

ProductID	SalesYear	TotalSales
1	2023	2500
1	2022	0
2	2023	6000
2	2022	4000

Final Query Result:

ProductID	SalesYear	TotalSales	PreviousYearSales	YoYGrowthPercentage
1	2023	2500	0	NULL
2	2023	6000	4000	50.0

This result shows the total sales for each product by year, the sales of the previous year, and the year-over-year growth percentage.

You can execute this query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to calculate the year-over-year growth for each product.