Time-Series Queries

Moving Average

Smooth Daily Values

A window frame can compute a rolling average over ordered days.

Program

Play the script to choose the first day included in a two-day moving average.

moving_average_days.sql 
CREATE TABLE daily_sales (day TEXT, total INTEGER);
INSERT INTO daily_sales VALUES ('2026-03-01', 100), ('2026-03-02', 140), ('2026-03-03', 120), ('2026-03-04', 180);
WITH params(start_day) AS (VALUES ()) SELECT day, total, ROUND(AVG(total) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW), 1) AS two_day_avg FROM daily_sales WHERE day >= (SELECT start_day FROM params) ORDER BY day;

CREATE TABLE daily_sales (day TEXT, total INTEGER);
INSERT INTO daily_sales VALUES ('2026-03-01', 100), ('2026-03-02', 140), ('2026-03-03', 120), ('2026-03-04', 180);
WITH params(start_day) AS (VALUES ()) SELECT day, total, ROUND(AVG(total) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW), 1) AS two_day_avg FROM daily_sales WHERE day >= (SELECT start_day FROM params) ORDER BY day;

CREATE TABLE daily_sales (day TEXT, total INTEGER);
INSERT INTO daily_sales VALUES ('2026-03-01', 100), ('2026-03-02', 140), ('2026-03-03', 120), ('2026-03-04', 180);
WITH params(start_day) AS (VALUES ()) SELECT day, total, ROUND(AVG(total) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW), 1) AS two_day_avg FROM daily_sales WHERE day >= (SELECT start_day FROM params) ORDER BY day;
window frame `ROWS BETWEEN 1 PRECEDING AND CURRENT ROW` uses the current and previous row.
ordered days `ORDER BY day` defines the sequence for the rolling calculation.
fixed dates The example uses literal ISO dates so replay output is deterministic.