Moving Averages Over a Sliding Window
Rolling N-period averages with BETWEEN PRECEDING AND CURRENT ROW.
The Moving Average Question
Analysts get asked this constantly: "Compute a 7-day moving average of revenue." A moving (or rolling) average smooths noisy daily data by averaging each point with its recent neighbors.
The interview-grade answer is a windowed AVG with an explicit sliding frame. The key skill is choosing the frame bounds so the window slides correctly along the order.
The Core Pattern
A moving average is AVG(value) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND CURRENT ROW). The frame slides: at each row it covers the current row and the previous n rows.
For a 7-day window over daily rows, you go back 6 rows plus the current one, giving 7 rows total. Off-by-one here is the most common mistake interviewers catch.
SELECT
sale_date,
amount,
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7d
FROM daily_sales;All lessons in this course
- Cumulative Sums With Window Frames
- ROWS vs RANGE Framing
- Moving Averages Over a Sliding Window
- Cumulative Distribution and Percent of Total