Rolling Average - SQL Programming

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.

                            wanted_product
                            wanted_product = 'app' (default)wanted_product = 'web'
                        

                            rolling_average.sql
                        
Replay: real traced execution (multi-file project)

                            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 ('app')) 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 ('web')) 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;

                        
1tables ← 1 row1CREATE TABLE daily_metrics (day TEXT, product TEXT, users INTEGER);2INSERT 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);⋯values this step1 rowtables
2daily_metrics ← 6 rows1CREATE TABLE daily_metrics (day TEXT, product TEXT, users INTEGER);2INSERT 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);3WITH params(wanted_product) AS (VALUES ('app')) 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;values this step6 rowsdaily_metrics
3result ← 3 rows⋯2INSERT 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);3WITH params(wanted_product) AS (VALUES ('app')) 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;values this step3 rowsresult
1tables ← 1 row1CREATE TABLE daily_metrics (day TEXT, product TEXT, users INTEGER);2INSERT 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);⋯values this step1 rowtables
2daily_metrics ← 6 rows1CREATE TABLE daily_metrics (day TEXT, product TEXT, users INTEGER);2INSERT 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);3WITH params(wanted_product) AS (VALUES ('web')) 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;values this step6 rowsdaily_metrics
3result ← 3 rows⋯2INSERT 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);3WITH params(wanted_product) AS (VALUES ('web')) 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;values this step3 rowsresult

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.