Calculating Running Totals & Moving Averages in SQL

Data analysis is rarely about looking at a single row in isolation. Most business questions involve context:

• "How does today's revenue compare to yesterday?"
• "What is our cumulative growth this month?"
• "What is the moving average trend?"

Before SQLite 3.25 (2018), this required painful self-joins. Now, we have Window Functions.

The Window: OVER()

The OVER clause defines a "window" of rows surrounding the current row.

Cumulative Sum (Running Total)

To calculate a running total, we sum up everything from the start until the current row.

SELECT 
  date, 
  amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM sales;

Interactive Example: Revenue Growth

Let's track the revenue of two different stores over time. We want to see:

1. Daily Revenue: What they made that day.
2. Cumulative Revenue: Total made since the beginning.

Understanding PARTITION BY

Notice PARTITION BY store_name above?

This tells SQL to reset the running total whenever it encounters a new store. Without it, Store B would start with Store A's total added to it!

Moving Averages

Business data is noisy. A "7-Day Moving Average" smooths out the spikes to show the true trend.

We use the ROWS BETWEEN frame clause:

AVG(amount) OVER (
  ORDER BY date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- 3-day average (2 days before + today)
)

Conclusion

Window functions like SUM() OVER and AVG() OVER are superpowers for reporting.

• ORDER BY inside OVER defines the sequence.
• PARTITION BY defines where to restart the calculation.
• ROWS BETWEEN defines the window size for moving metrics.