Moving average with a window frame
Compute a 7-day rolling average with AVG over a ROWS BETWEEN frame.
- SQL
- GreenSnippets
- MIT
SELECT
day,
revenue,
ROUND(
AVG(revenue) OVER (
ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
),
2
) AS revenue_7d_avg
FROM daily_revenue
ORDER BY day;Example / expected output
-- The first six rows average over fewer than 7 days.How it works
The frame clause decides which rows each result averages. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means the current row and the six before it, which is 7 days only if there is exactly one row per day. For gaps, first fill missing dates (see the missing-dates snippet) or use a RANGE frame with an interval.
This snippet has been reviewed but was not executed by an automated test. Test it in your own environment before relying on it.
Related snippets
- SQL
Percent of total with a window function
Show each row's share of the grand total or of its group without a self-join.
- SQL
Fill in missing dates with generate_series
Produce a row for every day in a range and left join your data so days with no activity show zero.
- SQL
Top N rows per group
Get the latest or highest N rows for each category using ROW_NUMBER in a subquery.
- SQL
RANK, DENSE_RANK and ROW_NUMBER
Understand how the three ranking window functions treat ties, with one query that shows all three.