To compute rolling averages in SQL (typically over the last N periods), the basic syntax is AVG(metric) OVER (ORDER BY dimension ROWS N-1 PRECEDING), with variations depending on the window over which you want to calculate the average.
-- Get Google Analytics sample data for July 2017
WITH sub1 AS (
SELECT PARSE_DATE("%Y%m%d", date) AS day,
SUM(totals.visits) AS sessions
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_201707*`
GROUP BY day
ORDER BY day
LIMIT 10
)
SELECT day,
sessions,
-- Rolling average over 3 days, including current day
AVG(sessions) OVER (ORDER BY day ROWS 2 PRECEDING) AS sessions_3d,
-- Rolling average over the last 3 days, excluding current day
AVG(sessions) OVER (ORDER BY day ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS sessions_last_3d,
-- Cumulative moving average over full preceding period
AVG(sessions) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING) AS sessions_avg_ctd
FROM sub1| day | sessions | sessions_3d | sessions_last_3d | sessions_avg_ctd |
|------------|----------|-------------|------------------|------------------|
| 2017-07-01 | 2048 | 2048.000000 | NaN | 2048.000000 |
| 2017-07-02 | 1895 | 1971.500000 | 2048.000000 | 1971.500000 |
| 2017-07-03 | 2046 | 1996.333333 | 1971.500000 | 1996.333333 |
| 2017-07-04 | 1938 | 1959.666667 | 1996.333333 | 1981.750000 |
| 2017-07-05 | 2885 | 2289.666667 | 1959.666667 | 2162.400000 |
| 2017-07-06 | 2658 | 2493.666667 | 2289.666667 | 2245.000000 |
| 2017-07-07 | 2450 | 2664.333333 | 2493.666667 | 2274.285714 |
| 2017-07-08 | 1859 | 2322.333333 | 2664.333333 | 2222.375000 |
| 2017-07-09 | 1921 | 2076.666667 | 2322.333333 | 2188.888889 |
| 2017-07-10 | 2769 | 2183.000000 | 2076.666667 | 2246.900000 |