Rolling Average

Hook: Interviewers love this one because it checks whether you can make SQL look backward across rows instead of only answering one row at a time.

Question: What is a rolling average in SQL, and how do you calculate it with window functions?

Answer: A rolling average is the average of the current row plus a fixed set of nearby rows, usually the previous N rows. In SQL, you normally calculate it with AVG(...) OVER (...) and an explicit window frame such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. The important idea is that a window function keeps every row in the result, instead of collapsing rows like GROUP BY.

Interview-Ready Answer: I’d say a rolling average is a moving average computed over a sliding window of rows. I’d write it with AVG(value) OVER (ORDER BY time_col ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) for a 3-row window. That gives each row its own average while preserving row-level detail. One detail I’d call out is that ROWS counts physical rows, while RANGE behaves differently around duplicate sort values, so I choose the frame explicitly.

Detailed Explanation: A rolling average is just a sliding lens over ordered data. Imagine a 3-row window moving across a list: for each row, you average that row and the two rows before it. This is the kind of question interviewers use to see whether you understand window functions, which are functions that calculate across a set of rows while still returning one output row per input row.

How it works under the hood

1. The database first orders the rows by the column inside the window’s ORDER BY clause.
2. For each row, it builds a frame, which means the exact subset of rows that will be used for that calculation.
3. For a 3-row rolling average, the frame is usually ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
4. The engine applies AVG to the values inside that frame and writes the result next to the original row.
5. If you add PARTITION BY, the process restarts inside each group, such as each customer, store, or product.

Why the frame matters

The most common mistake is forgetting the frame entirely. In many SQL engines, AVG(x) OVER (ORDER BY t) is cumulative, not rolling, because the default frame grows from the first row up to the current row. That means early values keep influencing later rows forever. For a true rolling average, you must define a fixed-width frame.

Use ROWS when you want a simple trailing count, like the last 7 sales rows. Use RANGE only when your database and use case truly need value-based grouping, because rows with the same sort key can all enter the frame together. That is why duplicate timestamps can surprise people.

When and why to use it

Rolling averages are great for smoothing noisy data: daily sales, request latency, conversion rate, temperature, or fraud score trends. They help you see the signal without being fooled by one spike. If the business question is, What is the recent trend?, a rolling average is usually better than a raw daily number.

Performance and complexity

From a practical point of view, the expensive part is usually the sort. The engine sorts each partition by the ordering column, then scans through it once to produce the window values. A good mental model is O(n log n) for the sort and about O(n) for the window pass, though real engines may use extra memory and can spill to disk on large partitions. If you have a matching index on (partition_key, order_key), the engine may read data in the right order and reduce sorting work.

Important edge cases

• The first rows have fewer than N prior rows, so the average is based on fewer values unless you filter them out.
• AVG ignores NULL values. If missing data should count as zero, use COALESCE before averaging, but only if zero is the right business meaning.
• Gaps in dates matter. ROWS counts records, not calendar days, so a missing day does not create an empty slot.
• If your sort column is not unique, add a tie-breaker column so the row order is deterministic.

Real-World Story: A checkout platform wants a 15-minute rolling average of payment success rate on its dashboard. The goal is to detect a gateway problem quickly, not to average the whole day. An engineer accidentally writes a cumulative average instead of a rolling one, so the dashboard stays near 99% even while the last 15 minutes are failing.

What goes wrong: alerts fire late, support tickets pile up, and logs show a burst of 502 and timeout errors from the payment provider. Users report that cards are failing, but the dashboard looks calm because old healthy traffic is diluting the recent outage. Once the query is fixed to use a real rolling frame, the drop appears within minutes and the on-call engineer sees the problem immediately.

That is the real value of rolling averages: they make recent behavior visible, which is exactly what operations teams and product teams need when the system changes fast.

Follow-up & Tricky Questions:

• How do you make it per customer? Add PARTITION BY customer_id so each customer gets their own independent rolling window.
• How do you make it 7 days instead of 7 rows? Use a time-aware approach if your database supports it, or join to a calendar table; rows are not the same thing as days when dates are missing.
• What if I only want complete windows? Use COUNT(*) OVER (...) to hide the first few partial frames until the window is full.
• How do you handle ties in the sort column? Add a unique tie-breaker, such as an ID, so the row order is stable and the rolling result is repeatable.
• Why not use a self-join? You can, but it is usually harder to read and often slower than a native window function.
• What is the difference between rolling and cumulative average? Rolling uses a fixed-width trailing window; cumulative keeps expanding from the start, so earlier rows always remain in the calculation.
• Does AVG treat NULL as zero? No. AVG ignores NULL, so you need COALESCE if zero is the intended value.
• Is AVG(x) OVER (ORDER BY t) a rolling average? Usually no. In many engines it is cumulative unless you explicitly set the frame.
• What happens with duplicate timestamps and RANGE? Rows with the same ordered value can all be included together, which makes the window wider than many candidates expect.

Common Mistakes:

• Forgetting ORDER BY. Without a stable order, a rolling average has no meaningful sequence. Fix: always order by the time or sequence column.
• Using GROUP BY instead of a window. GROUP BY collapses rows, but rolling averages need one output per input row. Fix: use AVG(...) OVER (...).
• Leaving the frame implicit. That often produces a cumulative average instead of a rolling one. Fix: write ROWS BETWEEN N PRECEDING AND CURRENT ROW explicitly.
• Ignoring NULL and tie behavior. NULLs are skipped by AVG, and duplicate sort values can change the frame shape. Fix: decide whether missing data is zero and add a tie-breaker column.

Memory Hook: Think of a rolling average like a treadmill window: the belt keeps moving forward, but you only stand on the last few steps.

Cheat Sheet:

• AVG(...) OVER (...) keeps row detail.
• ROWS means last N rows.
• RANGE is value-based and can include peers.
• Always choose the frame explicitly for a true rolling average.
• AVG ignores NULL, so missing data needs a business rule.
• Add PARTITION BY when each group needs its own window.

Practice Tasks:

• Change the query to compute a 7-row rolling average instead of 3 rows.
• Add PARTITION BY for store_id so each store gets its own trend line.
• Write a version that shows results only after the full window is available.