learning-it by example

Back to the video

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.

Video #134 ยท SQL and analytics

Steps in the video

  1. 0:00 Two missing days turned 111.9 a day into 156.6
  2. 0:33 The idea
  3. 0:56 Setup
  4. 1:20 Fourteen days, eleven rows
  5. 1:48 Finding the gaps
  6. 2:07 The run
  7. 2:29 A calendar to fill the holes
  8. 3:07 Two moving averages
  9. 3:50 Resampling to weeks
  10. 4:38 What this does not show
  11. 5:00 Mistakes to avoid
  12. 5:26 My take and next steps
  13. 5:55 Recap

Files

  • run.py (20 lines)
  • sales.py (17 lines)
  • series.py (35 lines)

Download the code (zip)

Tested with

Python 3.12.15

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.

Sources

Learning IT by Example.