lesson 43 Window functions · advanced

Rolling averages & missing dates

Calculate rolling trends while accounting for missing dates.

the idea

A ROWS frame averages a fixed number of neighbouring rows. When readings are missing, “the previous three rows” can span many hours. A RANGE frame over a numeric time value (julianday) averages a fixed time span instead, however many readings it contains.

Dataset: IoT / Sensors

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Smooth with three row neighbors. Run this. Each average uses this reading plus the two previous rows—even if those rows are hours apart.the pattern

   example.sql Run

   sql›

   SELECT
     recorded_at,
     value,
     AVG(value) OVER (ORDER BY recorded_at, reading_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_last_three
   FROM sensor_readings
   WHERE device_id = 2
     AND recorded_at >= '2025-03-10'
     AND recorded_at < '2025-03-11';

2. 2

   Find long gaps between readings. Run this. LAG() measures hours since the prior reading on device 1; the largest values are outages, not normal spacing.

   step-2.sql Run

   sql›

   SELECT
     device_id,
     recorded_at,
     ROUND((julianday(recorded_at) - julianday(LAG(recorded_at) OVER (PARTITION BY device_id ORDER BY recorded_at))) * 24, 1) AS hours_since_previous
   FROM sensor_readings
   WHERE device_id = 1
   ORDER BY hours_since_previous DESC
   LIMIT 5;

3. 3

   Count readings in six hours. Run this. A RANGE frame on julianday() counts how many readings fell in the last six hours of clock time, not a fixed row count.

   step-3.sql Run

   sql›

   SELECT
     recorded_at,
     value,
     COUNT(*) OVER (ORDER BY julianday(recorded_at) RANGE BETWEEN 0.25 PRECEDING AND CURRENT ROW) AS readings_in_6h
   FROM sensor_readings
   WHERE device_id = 2
     AND recorded_at >= '2025-03-10'
     AND recorded_at < '2025-03-11';

4. 4

   Average over six clock hours. Run this. The same time-based frame averages values from the last six hours only—after a long gap, fewer rows enter the average.

   step-4.sql Run

   sql›

   SELECT
     recorded_at,
     value,
     ROUND(AVG(value) OVER (ORDER BY julianday(recorded_at) RANGE BETWEEN 0.25 PRECEDING AND CURRENT ROW), 2) AS avg_6h
   FROM sensor_readings
   WHERE device_id = 2
     AND recorded_at >= '2025-03-10'
     AND recorded_at < '2025-03-11';

common mistake

AVG over ROWS BETWEEN 2 PRECEDING AND CURRENT ROW after a three-hour outage mixes readings from before and after the gap as if they were consecutive.