End To End Case Study: Measurement Frameworks Questions

WITH events_local AS (
  -- convert timestamp to target timezone date; change 'UTC' -> 'America/Los_Angeles' if needed
  SELECT
    user_id,
    CAST((occurred_at AT TIME ZONE 'UTC') AT TIME ZONE 'UTC' AS DATE) AS day_date
  FROM events
  WHERE occurred_at >= CURRENT_DATE - INTERVAL '60' DAY  -- limit scan
),
daily_unique AS (
  -- dedupe users per day
  SELECT DISTINCT user_id, day_date
  FROM events_local
),
dau AS (
  SELECT day_date AS date, COUNT(*) AS dau
  FROM daily_unique
  WHERE day_date >= CURRENT_DATE - INTERVAL '29' DAY
  GROUP BY day_date
)
SELECT
  d.date,
  d.dau,
  -- count distinct users that appeared in the 30-day window ending on d.date
  (SELECT COUNT(DISTINCT du.user_id)
   FROM daily_unique du
   WHERE du.day_date BETWEEN d.date - INTERVAL '29' DAY AND d.date
  ) AS mau
FROM dau d
ORDER BY d.date;