Collaboration and Business Impact Questions

WITH months AS (
  -- generate each month in 2024, using UTC month boundaries
  SELECT generate_series('2024-01-01'::timestamptz,
                         '2024-12-01'::timestamptz,
                         interval '1 month') AS month_start
),
events_utc AS (
  -- normalize timestamps to UTC (change 'UTC' to your business timezone if needed)
  SELECT user_id,
         event_name,
         (event_timestamp AT TIME ZONE 'UTC')::timestamptz AS ts_utc
  FROM events
)
SELECT
  to_char(m.month_start, 'YYYY-MM') AS month,
  -- MAU: distinct users with any event in the calendar month
  COALESCE(mau.cnt, 0) AS mau,
  -- WAU: distinct users with any event in the 7 days ending on month_end
  COALESCE(wau.cnt, 0) AS wau,
  -- ratio with safe division (null if wau = 0)
  CASE WHEN COALESCE(wau.cnt,0) = 0 THEN NULL
       ELSE ROUND((mau.cnt::numeric / wau.cnt)::numeric, 4)
  END AS mau_wau_ratio
FROM months m
LEFT JOIN (
  SELECT m.month_start,
         COUNT(DISTINCT e.user_id) AS cnt
  FROM months m
  LEFT JOIN events_utc e
    ON e.ts_utc >= m.month_start
   AND e.ts_utc < (m.month_start + interval '1 month')
  GROUP BY m.month_start
) mau USING (month_start)
LEFT JOIN (
  SELECT m.month_start,
         COUNT(DISTINCT e.user_id) AS cnt
  FROM months m
  LEFT JOIN events_utc e
    ON e.ts_utc >= ( (m.month_start + interval '1 month') - interval '6 days' )  -- 7-day window ending on last day
   AND e.ts_utc <  (m.month_start + interval '1 month')                           -- exclusive upper bound
  GROUP BY m.month_start
) wau USING (month_start)
ORDER BY month_start;