How do I calculate a 3-month moving average in SQL?
Smooth out Centro's sales with an average of the last 3 months, writing the window frame yourself.
Free course. Your Maestro, an AI, .
Monthly sales go up and down. A moving average smooths out those jumps: for each month, you average that month and the two before it. That makes the trend easier to see.
It's a window with a frame you write yourself:
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: the two previous rows plus the current one, three in all.
As on the previous page, ROWS counts rows. And since Centro has one record per month, three rows are three months.
AVG(ventas) OVER (
ORDER BY mes
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)Try it
Calculate Centro's 3-month moving average, rounded to a whole number with ROUND(...). First, answer the question below. If your average comes out like a running average that never lets go of January, ask your Maestro.
Check yourself: Before you run it: what will promedio_3_meses show for January, when there aren't two earlier months yet?
For the Centro branch, return mes, ventas and promedio3meses: the average of that month and the two before it, rounded to a whole number with ROUND(...). Sort by mes. You're done when you see 12 rows and March shows 166500.
Your turn
For the Centro branch, return mes, ventas and promedio_3_meses: the average of that month and the two before it, rounded to a whole number with ROUND(...). Sort by mes. You're done when you see 12 rows and March shows 166500.
Tables: ventas_mensuales (sucursal, mes, ventas)
ventas_mensuales · 35 rows
| Column | Type |
|---|---|
| sucursal Nombre de la sucursal: Centro, Norte o Sur. | VARCHAR |
| mes Primer día del mes que resume la fila. | DATE |
| ventas Ventas del mes, en pesos. | BIGINT |
First rows
| sucursal | mes | ventas |
|---|---|---|
| Centro | 2025-01-01 | 165100 |
| Centro | 2025-02-01 | 155700 |
| Centro | 2025-03-01 | 178700 |
Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.
Ready to run your query.
Row by row
Now the frame slides: instead of always starting in January, it starts two rows before the current one. Here's how it moved over Centro's first months when the solution ran in Maestro's sandbox:
| mes | ventas | rows in the frame | promedio_3_meses |
|---|---|---|---|
| 2025-01-01 | 165100 | January | 165100 |
| 2025-02-01 | 155700 | January and February | 160400 |
| 2025-03-01 | 178700 | January, February and March | 166500 |
| 2025-04-01 | 175800 | February, March and April | 170067 |
By April, January has left the frame. That's the difference from the running total two pages back: there the start of the frame was pinned to UNBOUNDED PRECEDING, and here it moves with every row.
In the chart, the sales line jumps around and the 3-month average follows it more calmly. The average comes from Vega-Lite's mean window with a frame from two rows before to the current one.
Look at January and February again: the frame asks for "up to two rows before," and at the start there aren't any. You don't get NULL; you get the average of the rows that are there.
What breaks in real life
Incomplete averages. If your report should only show averages of three full months, count the frame's rows with COUNT(*) over the same window and keep the ones with 3. In the sandbox, this query returned 10 rows, March through December:
SELECT mes, ventas,
ROUND(AVG(ventas) OVER w) AS promedio_3_meses
FROM ventas_mensuales
WHERE sucursal = 'Centro'
WINDOW w AS (ORDER BY mes ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
QUALIFY COUNT(*) OVER w = 3
ORDER BY mes;A missing month. Sur has no June. With ROWS, July's frame is the three most recent rows that do exist: April, May and July. If what you want is three calendar months, RANGE accepts a distance in the ORDER BY value: RANGE BETWEEN INTERVAL 2 MONTH PRECEDING AND CURRENT ROW takes the rows whose mes is two months away or less. In the sandbox, Sur came out like this:
| mes | ventas | average with ROWS | average with RANGE | rows in RANGE |
|---|---|---|---|---|
| 2025-05-01 | 119600 | 117767 | 117767 | 3 |
| 2025-07-01 | 117600 | 119400 | 118600 | 2 |
| 2025-08-01 | 122900 | 120033 | 120250 | 2 |
| 2025-09-01 | 114500 | 118333 | 118333 | 3 |
In July and August the two averages split: RANGE finds no June and averages two months, while ROWS reaches back to April to gather three rows. Neither is wrong; they answer different questions.
Check it yourself
First, answer the question below. If your RANGE throws an error or changes nothing, ask your Maestro.
Check yourself: With RANGE BETWEEN INTERVAL 2 MONTH PRECEDING AND CURRENT ROW, how many rows go into Sur's July frame?
For the Sur branch, return mes, ventas, promrows (the average with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), promrange (with RANGE BETWEEN INTERVAL 2 MONTH PRECEDING AND CURRENT ROW) and mesesenrange (COUNT() with the same RANGE frame). Round the averages to whole numbers with R
Your turn
For the Sur branch, return mes, ventas, prom_rows (the average with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), prom_range (with RANGE BETWEEN INTERVAL 2 MONTH PRECEDING AND CURRENT ROW) and meses_en_range (COUNT(*) with the same RANGE frame). Round the averages to whole numbers with ROUND(...) and sort by mes. You're done when July shows 119400, 118600 and 2.
Tables: ventas_mensuales (sucursal, mes, ventas)
ventas_mensuales · 35 rows
| Column | Type |
|---|---|
| sucursal Nombre de la sucursal: Centro, Norte o Sur. | VARCHAR |
| mes Primer día del mes que resume la fila. | DATE |
| ventas Ventas del mes, en pesos. | BIGINT |
First rows
| sucursal | mes | ventas |
|---|---|---|
| Centro | 2025-01-01 | 165100 |
| Centro | 2025-02-01 | 155700 |
| Centro | 2025-03-01 | 178700 |
Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.
Ready to run your query.
A centered average. If the average is for seeing the shape of the series rather than reporting the latest number, you can put the month in the middle: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING. In the sandbox, Centro's March came out 170067, the same as April's 3-month average, because it's the same three rows.
The classic interview question is "calculate the 7-day moving average of active users." The trap is the same one as with Sur: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW counts 7 rows, and those are 7 days only if there's one row per day with no gaps. Say out loud what you're assuming, and offer the version with RANGE and INTERVAL, or fill in the calendar as in module 3. If you want to practice this question, ask your Maestro to quiz you.
Before you put a moving average on a dashboard, check two things: whether the first periods should be shown (or filtered out with COUNT(*)), and whether your series has gaps. Write the full frame in the query, even when it's the usual one, so whoever reads it knows what you decided.
ROUND(AVG(ventas) OVER (
ORDER BY mes
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)) AS promedio_centradoCheck yourself: One more function that depends on the default frame: for Centro, what does LAST_VALUE(ventas) OVER (ORDER BY mes) return on the March row?
Common mistake: LAST_VALUE and the default frame
With ORDER BY and no frame written, the frame ends at the current row. That's why LAST_VALUE(ventas) OVER (ORDER BY mes) doesn't return December: on every row it returns that row's own sales. In a run on Centro's data, March came out 178700: its own sales. For the last value of the whole partition, write the full frame: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.