21. Running Sales Total
SELECT SalesDate,
Amount,
SUM(Amount)
OVER(ORDER BY SalesDate)
RunningTotal
FROM Sales;
22. Month-over-Month Growth
SELECT Month,
Sales,
LAG(Sales)
OVER(ORDER BY Month)
PrevMonth
FROM Sales;
23. YoY Growth
SELECT Year,
Sales,
LAG(Sales)
OVER(ORDER BY Year)
PrevYear
FROM Sales;
24. Highest Sales Month
SELECT TOP 1
Month,
SUM(Sales)
FROM Sales
GROUP BY Month
ORDER BY SUM(Sales) DESC;
25. Lowest Sales Month
Same logic with ascending sort.
26. Average Monthly Sales
SELECT AVG(MonthSales)
FROM
(
SELECT SUM(Sales) MonthSales
FROM Sales
GROUP BY Month
)x;
27. Rolling 3-Month Average
SELECT Month,
AVG(Sales)
OVER
(
ORDER BY Month
ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW
)
FROM Sales;
28. Moving Average
Common BI Architect question.
Uses:
AVG() OVER()
29. Detect Sales Drop
SELECT *
FROM
(
SELECT Month,
Sales,
LAG(Sales)
OVER(ORDER BY Month) PrevSales
FROM Sales
)x
WHERE Sales < PrevSales;
30. Best Growth Month
Calculate MoM difference.
