🧠 Rolling Averages: Smooth Out the Trend

Xorthax has now seen totals that build over time. But sometimes a running total gets too large to be useful.

Instead of asking “how much so far?”, he wants to ask:

“What’s the average over the last few rows?”

That’s a rolling average.

🚀 Rolling Averages with AVG() OVER()

A rolling average calculates an average across a small window that moves row by row.

AVG(column) OVER (
  ORDER BY column
  ROWS BETWEEN n PRECEDING AND CURRENT ROW
)

This works because:

• ORDER BY defines the sequence
• The frame limits which rows are included
• The average is recalculated at each row

📊 Example: Average of the Current Row and Two Before It

🧾 Sample Data

SELECT OrderDate, Amount,
       AVG(Amount) OVER (
         ORDER BY OrderDate
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS RollingAvg
FROM Orders;

Result:

Each row shows the average for a small moving window, not the entire dataset.

🧠 What’s Happening Here?

ORDER BY OrderDate puts the rows in sequence from earliest to latest.

Then the frame tells SQL which rows to include for each calculation.

In this example, the frame includes:

• The current row
• The two rows before it

That means the window slides forward one row at a time.

Early rows use fewer values because there aren’t yet two previous rows available.

This is what makes rolling averages useful: they smooth out short-term ups and downs without using all rows in the table.

🧪 Practice What You Just Learned

SELECT orderid, orderdate, totalamount, AVG(totalamount) OVER (ORDER BY orderdate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_avg FROM orders ORDER BY orderdate;

➡️ Next Step

So far, you’ve used the most common frame patterns.

Next, you’ll look at more window frame options, including ways to look forward and define broader ranges.

Want to go deeper?