Calculating Running/Moving Average in SQL

Sometimes you'll want to compute a running average over a selection of rows for the past N number of time periods. The running average is also called "moving average" or "rolling average".

The reason to use a running average is to smooth out the highs and lows of the data set and get a feel for the trends in the data.

Let's consider Amazon's quarterly revenues for the years 2001 to 2008 in a table "amazon_revenue":

quarter revenue
2001-1 700.356
2001-2 667.625
2001-3 639.281
2001-4 1115.171
... ...
2008-3 4265
2008-4 6703

When we plot the revenue as a timeseries, as expected, we see huge revenue jumps in the fourth quarter of the year (due to black friday and holday day shopping.)

To smooth out these huge jumps in revenue, we can compute a moving average that averages the previous three periods:

select quarter, 
       avg(revenue) over (order by quarter rows between 3 preceding and current row)
from amazon_revenue;

👋 No fuss, just SQL We are open sourcing everything from the experience working with our agency clients. They spend thousands of dollars to get this level of detailed analysis – which you can now get for free. We send one update every week. Join 400+ data analysts who are leveling up with our recipes. 👊

No spam, ever! Unsubscribe any time. See past emails here.

results matching ""

    No results matching ""