Moving Average

Hook: Interviewers love this question because it looks simple, but it quietly tests whether you really understand window frames, ordering, and the difference between a trailing average and a running average.

Question: How do you calculate a moving average in SQL?

Answer: A moving average is a rolling average over a fixed set of ordered rows, such as the last 3 sales or last 7 days. In SQL, you usually use a window function like AVG(... ) OVER (...) with a frame such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. The key idea is that the query keeps each row in the output while looking backward over a small window of nearby rows.

Interview-Ready Answer: I would calculate it with a window function. For example, I’d use AVG(value) OVER (ORDER BY date_column ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for a 7-row trailing average, and I’d add PARTITION BY if I needed it per product or per customer. One important detail is that I use ROWS when I want an exact number of rows, because the default ordered window frame can behave like a running average instead of a true moving average.

What a moving average really is

A window function is a function that looks at related rows without collapsing them into one result row. A frame is the exact subset of rows the function can see for the current row. For a moving average, that frame usually slides forward one row at a time.

1. The database orders the rows by the column you choose, such as sold_on or created_at.
2. For each current row, the engine defines a frame like the last 7 rows, or the last 30 rows, or the current row plus 2 rows before it.
3. The aggregate, usually AVG, is computed only over that frame.
4. The frame then slides to the next row and the process repeats.
5. The output keeps every original row, which is why window functions are so useful for dashboards and trend analysis.

Trailing, running, and centered averages

A lot of candidates say “moving average” when they really mean “running average.” A running average includes everything from the start up to the current row. A moving average includes only a fixed-size neighborhood. A centered average looks both backward and forward, which is useful for charts but awkward for live systems because future rows are not available yet.

ROWS vs RANGE

This is the main gotcha. ROWS counts physical rows. RANGE groups rows with the same sort key value, which means duplicates can change the result in ways that surprise people. If you want a true 7-row or 30-row moving average, use ROWS and make the order deterministic with a tie-breaker when needed.

When to use it

• To smooth noisy metrics like sales, traffic, latency, or sign-ups.
• To spot trends without reacting to one unusual spike.
• To power charts where users want a cleaner line than raw data.
• To compare a current value against recent history.

Performance and practical details

The expensive part is usually the sort on the partition and order keys. After that, the engine scans each partition and applies the window aggregate. In practice, the overall cost is often dominated by sorting, so think roughly O(n log n) for the sort plus a linear pass, although many engines optimize bounded frames well. A small frame like 7 or 30 rows is usually cheap; very large partitions can still spill to disk if memory is tight.

If you are using PostgreSQL, the sort may spill when work memory is too small. In MySQL, window functions are available starting in 8.0; older versions usually need a self-join, which is slower and harder to read. The same idea exists across modern SQL engines, but the exact syntax and optimizer behavior can differ a bit.

Important edge cases

• Missing dates: 7 rows is not the same as 7 calendar days. If dates are missing, join to a calendar table first.
• NULL values: AVG ignores NULLs, so the result may be based on fewer points than you expect.
• Ties in order: If two rows have the same timestamp, add a second sort key like an ID to make the order deterministic.
• Early rows: The first few rows have smaller frames because there is not enough history yet; that is normal.

Memory note: think of a moving average like a sliding camera window over a train. You are not filming the whole train at once; you are only looking at the few cars currently in view.

Real-World Story: Imagine a checkout service at an e-commerce company. The ops team tracks a 7-row moving average of daily order volume and payment failures so they can tell whether a spike is real or just noise. The dashboard is refreshed every morning, and product managers use it to decide whether a promotion is helping or hurting conversion.

Now picture the bug: an engineer writes AVG(order_count) OVER (ORDER BY day) and assumes it is a moving average. Because the default ordered frame behaves like a running average and duplicate dates are treated as peers, the chart looks flatter than it should and then jumps in strange places. Users start asking why Friday looks identical to Thursday, support tickets mention inconsistent totals, and the log line shows the query returning the same value for multiple rows on the same date. The outage is not a crash; it is a trust problem, because the team makes bad decisions from misleading numbers.

The fix is simple but important: use an explicit ROWS frame, add a tie-breaker to the ORDER BY, and if the business wants calendar days instead of rows, build the data onto a complete date spine first.

Follow-up & Tricky Questions:

• How do you calculate a moving average per product or per store? Add PARTITION BY product_id or PARTITION BY store_id so each group gets its own sliding window.
• What is the difference between ROWS and RANGE? ROWS counts rows, while RANGE groups peers with equal sort values; for moving averages, ROWS is usually the safer choice.
• How do you make a 7-day moving average when some dates are missing? Use a calendar table or date spine, left join your facts to it, and then compute the window over the complete daily series.
• Can you compute a centered moving average? Yes, for example ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING. That is useful for analysis, but not for real-time reporting because it needs future rows.
• Why might the first few rows show smaller averages? Because the frame has not fully filled yet. A 30-row window on row 3 can only use 3 rows, so the average is still valid but based on less history.
• Can you filter on the moving average in WHERE? Not directly, because window functions are evaluated after WHERE. Use a CTE or subquery, then filter in the outer query.

Tricky / Gotcha Questions:

• Why does AVG(x) OVER (ORDER BY t) not behave like a true moving average? Because the default ordered frame is usually a running-style frame, not a fixed trailing row count. You must spell out the frame explicitly to get a true moving average.
• Do NULLs count in a moving average? AVG ignores NULLs, so they do not contribute to the numerator or denominator. If you need to treat NULL as zero, use COALESCE intentionally.
• Is a 7-row moving average the same as a 7-day moving average? No. A row window counts records, not calendar time, so missing days can make the result cover more than 7 days.

Common Mistakes:

• Using the default frame by accident. Correction: write the frame explicitly with ROWS BETWEEN ... when you want a fixed-size moving average.
• Forgetting a tie-breaker in ORDER BY. Correction: add a stable key like sale_id so rows with the same timestamp are processed predictably.
• Thinking rows equal days. Correction: if the question is about time, use a calendar table when dates can be missing.
• Ignoring NULL behavior. Correction: remember that AVG skips NULLs; use COUNT(column) if you want to see how many points contributed.

Memory Hook: Picture a conveyor belt with a small basket on top: every new row drops one old row off the back and adds the newest row to the front. That is a moving average; a running average is the basket that never drops anything.

Cheat Sheet:

• AVG(value) OVER (ORDER BY time ROWS BETWEEN N PRECEDING AND CURRENT ROW) is the standard trailing moving average pattern.
• Add PARTITION BY to compute one moving average per group.
• ROWS counts rows; RANGE groups equal sort values.
• AVG ignores NULLs.
• Missing dates mean you need a date spine for true time-based windows.
• Window functions keep all rows; they do not aggregate away detail rows.

Practice Tasks:

• Compute a 7-row moving average of daily revenue for one store.
• Change the query to a centered 3-row moving average and observe how the first and last rows behave.
• Write a CTE that returns only rows where the moving average is above the current revenue.