DAU、WAU、製品の粘着性を計算する

WITH daily_users AS (
  SELECT DISTINCT
    date_trunc('day', timestamp_utc) AS activity_day,
    user_id
  FROM product_events
  WHERE timestamp_utc >= now() - INTERVAL '35 days'
    AND event_name IN ('feature_used', 'dashboard_viewed', 'query_run')
),
days AS (
  SELECT DISTINCT activity_day
  FROM daily_users
),
activity AS (
  SELECT
    days.activity_day,
    COUNT(DISTINCT CASE
      WHEN daily_users.activity_day = days.activity_day
      THEN daily_users.user_id
    END) AS dau,
    COUNT(DISTINCT daily_users.user_id) AS wau
  FROM days
  JOIN daily_users
    ON daily_users.activity_day BETWEEN
      days.activity_day - INTERVAL '6 days' AND days.activity_day
  GROUP BY days.activity_day
)
SELECT
  activity_day,
  dau,
  wau,
  100.0 * dau / NULLIF(wau, 0) AS stickiness_pct
FROM activity
ORDER BY activity_day;