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 mesStart of line 3. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW End of line 3.)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?
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 |
Tab indents; Esc then Tab leaves
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. Centro has one row per month, with no gaps or NULL sales: here COUNT(*) = 3 selects three complete months. Before applying this to another table, check monthly uniqueness and a gap-free calendar. If ventas can be NULL, also require COUNT(ventas) = 3: AVG ignores those values even though COUNT(*) counts their rows. In the sandbox, this Centro query returned 10 rows, March through December:
SELECT mes, ventas, ROUND(AVG(ventas) OVER w) AS promedio_3_mesesFROM ventas_mensualesWHERE sucursal = 'Centro'WINDOW w AS (ORDER BY mes ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)Start of line 6. QUALIFY COUNT(*) OVER w = 3 End of line 6.ORDER BY mes;A missing month. Sur has no June. With ROWS, July's frame is the three most recent rows that do exist, including the current row: 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?
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 |
Tab indents; Esc then Tab leaves
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 whether to show the first periods, whether there is one row per period with no gaps, and whether there are NULL values. Counting three rows is not enough if one of the three measurements is missing. 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 mesStart of line 3. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING End of line 3.)) 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 explicit frame, DuckDB uses RANGE through the current row and its peers. Here mes is unique for Centro, so LAST_VALUE(ventas) OVER (ORDER BY mes) returns the current row's sales: March gave 178700. With ties, the frame includes peers and may return another tied row's value; it guarantees neither the current row's sales nor which peer comes last without a tiebreaker. For the last value of the whole partition, write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, and use a unique ordering if you need to choose a particular row.
There is one row per month: January = 10, February = NULL, and March = 30. Write the frame for the current row and the two preceding rows. For March, what do AVG(ventas), COUNT() and COUNT(ventas) return over that frame? Is counting three rows enough to call it an average of thre
Check yourself: There is one row per month: January = 10, February = NULL, and March = 30. Write the frame for the current row and the two preceding rows. For March, what do AVG(ventas), COUNT(*) and COUNT(ventas) return over that frame? Is counting three rows enough to call it an average of three complete months?