learning-it by example

#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.

1. In the made-up shop data with 14 days but only 11 rows, what average sale per day did SQL give for week one from the rows alone, and what did it give after filling the missing days with zero?
2. Why must you find and fill the gaps in a time series table before using window functions or weekly totals in SQL?
3. A three day moving average over the rows gave 157.7 on 7 March, while the same average over the filled calendar gave 70.0. What explains the difference?
4. In the gap query, how does SQL find that days are missing between two rows?
5. When is it right to fill a missing day with zero, according to the time series in SQL video?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. 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.

  2. 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.

  3. 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.

Up next in SQL and analytics

More in SQL and analytics

All SQL and analytics videos

Related videos