Find Products with Sales Growing Month-over-Month
Find Products with Sales Growing Month-over-Month
SalesWithGrowth Table:
OrderDetails Table:
Orders Table:
Query Explanation:
-
MonthlySalesCTE: Calculates the total quantity sold per product per month. -
LAG()function: Retrieves the quantity sold in the previous month for each product. -
Final SELECT: Filters the result to only include months where the current month's sales were greater than the previous month.
SQL Query:
WITH MonthlySales AS (
SELECT
p.ProductID,
p.ProductName,
FORMAT(o.OrderDate, 'yyyy-MM') AS SalesMonth,
SUM(od.Quantity) AS TotalQuantity
FROM
Products p
INNER JOIN OrderDetails od ON p.ProductID = od.ProductID
INNER JOIN Orders o ON od.OrderID = o.OrderID
GROUP BY
p.ProductID, p.ProductName, FORMAT(o.OrderDate, 'yyyy-MM')
),
SalesWithGrowth AS (
SELECT
ProductID,
ProductName,
SalesMonth,
TotalQuantity,
LAG(TotalQuantity) OVER (PARTITION BY ProductID ORDER BY SalesMonth) AS PrevMonthQuantity
FROM
MonthlySales
)
SELECT
ProductID,
ProductName,
SalesMonth,
TotalQuantity,
PrevMonthQuantity
FROM
SalesWithGrowth
WHERE
PrevMonthQuantity IS NOT NULL
AND TotalQuantity > PrevMonthQuantity;
Output:
