Data Infrastructure and Architecture Experience Questions

-- 1) Normalize to UTC (or your product timezone) and derive event_date
WITH normalized AS (
  SELECT
    user_id,
    DATE(TIMESTAMP_TRUNC(event_ts AT TIME ZONE 'UTC', DAY)) AS event_date, -- adjust zone as needed
    event_ts,
    COALESCE(ingestion_ts, CURRENT_TIMESTAMP) AS ingestion_ts
  FROM events
  WHERE event_ts IS NOT NULL
),

-- 2) DAU: unique users per day
dau AS (
  SELECT event_date, COUNT(DISTINCT user_id) AS dau
  FROM normalized
  WHERE ingestion_ts <= CURRENT_TIMESTAMP - INTERVAL '0 days'  -- placeholder for watermark policy
  GROUP BY event_date
),

-- 3) Users per day (for retention joins)
users_by_day AS (
  SELECT event_date, user_id
  FROM normalized
  GROUP BY event_date, user_id
),

-- 4) Compute 7-day retention: users on day t who are present on t+7
retention AS (
  SELECT
    d.event_date AS cohort_date,
    COUNT(r.user_id) AS retained_7d,
    COUNT(d.user_id) AS cohort_size
  FROM users_by_day d
  LEFT JOIN users_by_day r
    ON d.user_id = r.user_id
    AND r.event_date = d.event_date + INTERVAL '7 day'
  GROUP BY d.event_date
)

SELECT
  d.event_date,
  d.dau,
  COALESCE(retained_7d, 0) AS retained_7d,
  cohort_size,
  ROUND(100.0 * COALESCE(retained_7d,0) / NULLIF(cohort_size,0), 2) AS retention_7d_pct
FROM dau d
LEFT JOIN retention r ON d.event_date = r.cohort_date
ORDER BY d.event_date;