Analytical Query Patterns

Rolling Average

Smooth a Series

A window frame can average the current row with the previous row to smooth a time series.

Program

Play the script to choose a product and compare raw users with a two-day rolling average.

rolling_average.sql 
CREATE TABLE daily_metrics (day TEXT, product TEXT, users INTEGER);
INSERT INTO daily_metrics VALUES ('2026-01-01', 'app', 10), ('2026-01-02', 'app', 14), ('2026-01-03', 'app', 20), ('2026-01-01', 'web', 8), ('2026-01-02', 'web', 12), ('2026-01-03', 'web', 18);
WITH params(wanted_product) AS (VALUES ()) SELECT day, users, ROUND(AVG(users) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW), 1) AS rolling_avg FROM daily_metrics WHERE product = (SELECT wanted_product FROM params) ORDER BY day;

CREATE TABLE daily_metrics (day TEXT, product TEXT, users INTEGER);
INSERT INTO daily_metrics VALUES ('2026-01-01', 'app', 10), ('2026-01-02', 'app', 14), ('2026-01-03', 'app', 20), ('2026-01-01', 'web', 8), ('2026-01-02', 'web', 12), ('2026-01-03', 'web', 18);
WITH params(wanted_product) AS (VALUES ()) SELECT day, users, ROUND(AVG(users) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW), 1) AS rolling_avg FROM daily_metrics WHERE product = (SELECT wanted_product FROM params) ORDER BY day;
window frame `ROWS BETWEEN 1 PRECEDING AND CURRENT ROW` looks at the current row and one row before it.
rolling average `AVG(users) OVER (...)` computes a moving average without collapsing rows.
series order `ORDER BY day` defines the sequence used by the frame.