SQL Cumulative & Moving Aggregations (Running Totals & Rolling Averages)

🔹 Why These Aggregations Matter

Running totals and moving averages turn raw rows into trend insights. Whether you track revenue, conversions, or defect counts, window functions deliver progressive metrics without complicated self-joins.

⚙️ 1️⃣ Types of Window-Based Aggregations

⚙️ 2️⃣ Sales Dataset

⚙️ 3️⃣ Running Total per Region

SELECT 
  Region,
  Month,
  Sales,
  SUM(Sales) OVER (PARTITION BY Region ORDER BY Month) AS RunningTotal
FROM Sales;

⚙️ 4️⃣ Cumulative Count

SELECT 
  Region,
  Month,
  COUNT(*) OVER (PARTITION BY Region ORDER BY Month) AS MonthCount
FROM Sales;

⚙️ 5️⃣ Cumulative Percentage

SELECT 
  Region,
  Month,
  Sales,
  SUM(Sales) OVER (PARTITION BY Region ORDER BY Month) * 100.0 /
  SUM(Sales) OVER (PARTITION BY Region) AS CumulativePercent
FROM Sales;

⚙️ 6️⃣ 3-Month Moving Average

SELECT 
  Region,
  Month,
  Sales,
  AVG(Sales) OVER (
      PARTITION BY Region 
      ORDER BY Month 
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS MovingAvg3
FROM Sales;

⚙️ 7️⃣ Rolling Sum of Last Two Months

SELECT 
  Region,
  Month,
  Sales,
  SUM(Sales) OVER (
      PARTITION BY Region 
      ORDER BY Month 
      ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS TwoMonthSum
FROM Sales;

⚙️ 8️⃣ Running Totals with Dates

SELECT 
  Region,
  SaleDate,
  SUM(Sales) OVER (
      PARTITION BY Region
      ORDER BY SaleDate
  ) AS RunningTotal
FROM Sales;

🧩 Real-World Use Cases

⚖️ Best Practices

• Choose the right frame clause (ROWS BETWEEN) to define window size.
• Partition by logical groups such as region, customer, or application.
• Use chronological ordering columns to ensure accurate cumulative behavior.
• Avoid large unbounded frames when performance is critical.

💡 Interview Tip