Analytical Queries

Common Analytical Patterns

Analysts often need to compute running totals, moving averages, and period-over-period growth. Window functions are essential here.

1. Running Total

Calculate the cumulative sum of sales over time.

SELECT Date, Sales,
SUM(Sales) OVER (ORDER BY Date) as RunningTotal
FROM DailySales;

2. Moving Average

Calculate a 3-day moving average to smooth out fluctuations.

SELECT Date, Sales,
AVG(Sales) OVER (
    ORDER BY Date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) as MovingAvg_3Day
FROM DailySales;

3. Year-Over-Year Growth

Compare current year sales with previous year.

WITH YearSales AS (
    SELECT YEAR(OrderDate) as Year, SUM(TotalAmount) as Revenue
    FROM Orders
    GROUP BY YEAR(OrderDate)
)
SELECT Year, Revenue,
LAG(Revenue) OVER (ORDER BY Year) as PrevYearRevenue,
(Revenue - LAG(Revenue) OVER (ORDER BY Year)) / LAG(Revenue) OVER (ORDER BY Year) * 100 as GrowthPercent
FROM YearSales;