Sales_Trends.sql

/* Which product categories generated the highest monthly sales in 2013,
and how do they rank month‑over‑month?*/
USE AdventureWorks2022;
GO
SELECT 
    pc.Name AS ProductCategory,
    YEAR(soh.OrderDate) AS SalesYear,
    MONTH(soh.OrderDate) AS SalesMonth,
    SUM(sod.LineTotal) AS MonthlySales,
    RANK() OVER (PARTITION BY YEAR(soh.OrderDate) ORDER BY SUM(sod.LineTotal) DESC) AS CategoryRank
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) = 2013
GROUP BY pc.Name, YEAR(soh.OrderDate), MONTH(soh.OrderDate)
ORDER BY CategoryRank, SalesMonth;

-- Monthly sales trend for Bikes category in 2013
SELECT 
    YEAR(soh.OrderDate) AS SalesYear,
    MONTH(soh.OrderDate) AS SalesMonth,
    SUM(sod.LineTotal) AS MonthlySales
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 pc.Name = 'Bikes'
  AND YEAR(soh.OrderDate) = 2013
GROUP BY YEAR(soh.OrderDate), MONTH(soh.OrderDate)
ORDER BY SalesYear, SalesMonth;

-- Insight: Bikes dominate monthly sales, showing strong seasonality.
-- Insight: Accessories remain stable, contributing consistently across months.