KPI Frameworks and Governance Questions

-- Postgres-style solution; adjust date_trunc/timezone functions per DB
WITH events AS (
  SELECT
    user_id,
    -- normalize to UTC or business timezone, then truncate to week starting Monday
    date_trunc('week', (event_time AT TIME ZONE 'UTC')::timestamp) AS week_start_utc
  FROM user_events
  -- filter noisy data if needed, e.g. event_time IS NOT NULL
),
weekly_users AS (
  -- deduplicate: one row per user per week
  SELECT DISTINCT user_id, week_start_utc
  FROM events
),
wau AS (
  SELECT
    week_start_utc AS week_start,
    COUNT(*) AS wau
  FROM weekly_users
  GROUP BY week_start_utc
),
retention AS (
  -- count users in current week who were also active previous week
  SELECT
    cur.week_start_utc AS week_start,
    COUNT(prev.user_id) AS retained_from_prev_week
  FROM weekly_users cur
  LEFT JOIN weekly_users prev
    ON cur.user_id = prev.user_id
   AND prev.week_start_utc = cur.week_start_utc - INTERVAL '7 days'
  GROUP BY cur.week_start_utc
)
SELECT
  w.week_start,
  w.wau,
  -- retention percentage: retained / wau (handle division by zero)
  ROUND(100.0 * COALESCE(r.retained_from_prev_week,0) / NULLIF(w.wau,0), 2) AS retention_wow
FROM wau w
LEFT JOIN retention r USING (week_start)
ORDER BY week_start;