Category_Growth.sql

--How did each product category’s sales grow year‑over‑year between 2012 and 2013?

SELECT 
    pc.Name AS ProductCategory,
    YEAR(soh.OrderDate) AS SalesYear,
    SUM(sod.LineTotal) AS TotalSales,
    LAG(SUM(sod.LineTotal)) OVER (PARTITION BY pc.Name ORDER BY YEAR(soh.OrderDate)) AS PrevYearSales,
    (SUM(sod.LineTotal) - LAG(SUM(sod.LineTotal)) OVER (PARTITION BY pc.Name ORDER BY YEAR(soh.OrderDate))) AS GrowthAmount,
    ((SUM(sod.LineTotal) - LAG(SUM(sod.LineTotal)) OVER (PARTITION BY pc.Name ORDER BY YEAR(soh.OrderDate))) * 100.0 
        / LAG(SUM(sod.LineTotal)) OVER (PARTITION BY pc.Name ORDER BY YEAR(soh.OrderDate))) AS GrowthPercent
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product p ON sod.ProductID = p.ProductID
JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE YEAR(soh.OrderDate) BETWEEN 2012 AND 2013
GROUP BY pc.Name, YEAR(soh.OrderDate)
ORDER BY pc.Name, SalesYear;
--Insight: Year‑over‑year analysis reveals which product categories expanded and which 
--declined between 2012 and 2013, highlighting growth opportunities and areas needing attention.