milestone 2/4 Rolling averages & missing dates

Calculate rolling trends while accounting for missing dates.

Video Product Analytics · Move from watch events to daily engagement, rolling trends, and signup-cohort retention.

approach

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.

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;

data for this milestone