lesson 43 Window functions · advanced

Rolling averages & missing dates

Calculate rolling trends while accounting for missing dates.

the idea

A rolling average smooths variation. ROWS counts rows, not calendar days. When dates are missing, a three-row average differs from a three-day average. Fill missing days with zero only if the business definition says absence means no activity.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

daily_metrics2 columns · 5 rows

daytext · key	watch_minutesinteger
2025-01-01	30
2025-01-02	60
2025-01-04	90
2025-01-05	120
2025-01-07	60

all 5 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

SELECT
  day,
  watch_minutes,
  ROUND(AVG(watch_minutes) OVER( ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ),2) AS rolling_avg
FROM daily_metrics;

Watch for: Three rows are not three days when dates are missing.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Notice missing dates. Jan 3 and Jan 6 are absent. A row frame skips those dates.
step-1.sql
sql›
```
SELECT *
FROM daily_metrics
ORDER BY day;
```

Average observed rows. For Jan 4, the frame is Jan 1, Jan 2, Jan 4. That is three rows, not three calendar days.

step-2.sql

SELECT
  day,
  watch_minutes,
  ROUND(AVG(watch_minutes) OVER(ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2) AS rolling_avg
FROM daily_metrics
ORDER BY day

Build a complete calendar. Missing days become zero under the explicit business rule.

step-3.sql

WITH RECURSIVE days(day) AS(
  SELECT '2025-01-01'
  UNION ALL
  SELECT date(day,'+1 day')
  FROM days
  WHERE day<'2025-01-07'
)
SELECT d.day,COALESCE(m.watch_minutes,0) AS watch_minutes
FROM days d
LEFT JOIN daily_metrics m ON m.day=d.day
ORDER BY d.day;

Average calendar days. Now each row is one day. The frame’s three rows are also three days.

step-4.sql

WITH RECURSIVE days(day) AS(
  SELECT '2025-01-01'
  UNION ALL
  SELECT date(day,'+1 day')
  FROM days
  WHERE day<'2025-01-07'
), complete AS(
  SELECT d.day,COALESCE(m.watch_minutes,0) AS watch_minutes
  FROM days d
  LEFT JOIN daily_metrics m ON m.day=d.day
)
SELECT
  day,
  watch_minutes,
  ROUND(AVG(watch_minutes) OVER(ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2) AS rolling_avg
FROM complete
ORDER BY day