#134 SQL and analytics 6.4 min
Time Series in SQL: Gaps, Windows, Resampling
Two missing days made SQLite report 156.6 in sales a day for a week that averaged 111.9 once the gaps were filled.
Fill one gap yourself
Copy the code folder first. In sales.py add a row for 2026-03-05 with amount 100, right after the 4 March row, then run python run.py gaps and python run.py weekly. Predict first how many gaps are left and what week one will average. When I ran it, two gaps were left, 5 March to 7 March and 10 March to 12 March, each with 1 missing day, and week one averaged 147.2 from 6 rows and 126.1 from the 7-day calendar.
A hint
Keep the same tuple format, for example ("2026-03-05", 100), with a comma after it.
Change what a missing day means
In series.py change COALESCE(s.amount, 0) to COALESCE(s.amount, 100) and run python run.py weekly. Predict how the filled week one total and average change, and whether the rows-only table changes. When I ran it, filled week one became 983 over 7 days, 140.4 a day, week two became 1120, 160.0, and the rows-only table stayed as before.
A hint
The rows-only query never reads the calendar, so it cannot change.
Widen the moving average window
In series.py change ROWS 2 PRECEDING to ROWS 6 PRECEDING in both places, then run python run.py moving. The label still says 3-day, but the window is now 7 rows. Predict what happens to the first rows. When I ran it, the rows-only column on 2026-03-07 gave 148.3 and the filled column gave 98.8.
A hint
Remember the first few days average fewer values than the window size.
These tasks are for you to try on your own machine. Nothing is checked or saved.
What this does not show
Fourteen made-up days checked only in SQLite, with no times of day or time zones, where a missing day is treated as zero sales by choice.
