Moving Averages
intermediatewindow-functions
2 min read
What is a Moving Average?
A moving average (also called a rolling average) computes the average of a set of values over a sliding window of rows. It is widely used to smooth noisy data, especially in time-series analysis.
Syntax
SELECT date_column, value,
AVG(value) OVER (
ORDER BY date_column
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3
FROM table_name;When to Use
- Smoothing daily sales data over a 7-day window
- Computing rolling averages for trend analysis
- Identifying patterns in time-series data
Key Points
- ROWS BETWEEN — Defines the window size: N PRECEDING AND CURRENT ROW for an N+1 row average.
- Trailing Average — The most common type includes only past rows and the current row.
- Centered Average — Use ROWS BETWEEN N PRECEDING AND N FOLLOWING for a centered window.
- NULL Handling — AVG ignores NULLs. Early rows with fewer than N preceding rows compute the average over available rows.
- Performance — Window functions are efficient because they scan the data once.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Moving Averages Challenge
Write a query that solve this task: calculate a rolling average over the current and 2 preceding rows.
Expected result
Each order with a 3-period moving average of the amount column.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is a Moving Average?
Initializing database...Each run starts from fresh sample data.
More Examples
5-period moving average of product prices
Smooth product prices over a 5-row window.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What happens at the beginning when there are fewer preceding rows?
The average is computed over whatever rows are available. The first row's moving average is just its own value.
Can I compute a moving sum instead of average?
Yes. Replace AVG with SUM: SUM(col) OVER (ORDER BY date ROWS BETWEEN N PRECEDING AND CURRENT ROW).