Team Context and Technical Landscape Questions

-- per-user event rate pre/post release
SELECT event_date,
       COUNT(*) AS total_events,
       COUNT(DISTINCT user_id) AS uniques,
       AVG(events_per_user) OVER (ORDER BY event_date) AS avg_epp
FROM (
  SELECT event_date, user_id, COUNT(*) AS events_per_user
  FROM events
  WHERE event_name = 'feature_use'
    AND event_date BETWEEN '2025-11-01' AND '2025-11-14'
  GROUP BY event_date, user_id
) t
GROUP BY event_date
ORDER BY event_date;

WITH
-- per-user first event date (date only, UTC)
user_first_event AS (
  SELECT
    user_id,
    MIN(DATE(occurred_at)) AS first_event_date
  FROM events
  GROUP BY user_id
),

-- user signup date (date only)
user_signup AS (
  SELECT user_id, DATE(created_at) AS signup_date
  FROM users
),

-- whether user signed up within 7 days prior to or on their first event (adjust per assumption)
user_signup_flag AS (
  SELECT
    f.user_id,
    f.first_event_date,
    s.signup_date,
    CASE
      WHEN s.signup_date IS NOT NULL
       AND DATE_DIFF(f.first_event_date, s.signup_date, DAY) BETWEEN 0 AND 7
      THEN 1 ELSE 0 END AS signed_within_7d
  FROM user_first_event f
  LEFT JOIN user_signup s USING (user_id)
),

-- whether user made a purchase within 7 days after their first event
user_purchase_flag AS (
  SELECT
    f.user_id,
    f.first_event_date,
    CASE WHEN EXISTS (
      SELECT 1 FROM purchases p
      WHERE p.user_id = f.user_id
        AND DATE(p.occurred_at) BETWEEN f.first_event_date AND DATE_ADD(f.first_event_date, INTERVAL 7 DAY)
    ) THEN 1 ELSE 0 END AS purchased_within_7d
  FROM user_first_event f
),

-- combine flags per user
user_flags AS (
  SELECT
    f.user_id,
    f.first_event_date,
    COALESCE(s.signed_within_7d, 0) AS signed_within_7d,
    COALESCE(p.purchased_within_7d, 0) AS purchased_within_7d
  FROM user_first_event f
  LEFT JOIN user_signup_flag s USING (user_id, first_event_date)
  LEFT JOIN user_purchase_flag p USING (user_id, first_event_date)
),

-- daily event activity (distinct users per event date)
daily_events AS (
  SELECT DATE(occurred_at) AS event_date, user_id
  FROM events
  GROUP BY DATE(occurred_at), user_id
),

-- calendar of dates to report on (from events range)
calendar AS (
  SELECT day::date AS date
  FROM (
    SELECT GENERATE_SERIES(
      (SELECT MIN(DATE(occurred_at)) FROM events),
      (SELECT MAX(DATE(occurred_at)) FROM events),
      INTERVAL '1 day'
    ) AS day
  ) t
)

-- final aggregation: 7-day rolling DAU and 7-day rolling conversion for "new users"
SELECT
  c.date,
  -- 7-day rolling DAU: distinct users with an event in [date-6, date]
  (SELECT COUNT(DISTINCT de.user_id)
   FROM daily_events de
   WHERE de.event_date BETWEEN DATE_SUB(c.date, INTERVAL 6 DAY) AND c.date
  ) AS dau,
  -- 7-day rolling new-user conversion:
  -- numerator: distinct users whose first_event_date in window AND signed_within_7d=1 AND purchased_within_7d=1
  -- denominator: distinct users whose first_event_date in window AND signed_within_7d=1
  CASE
    WHEN denom = 0 THEN NULL
    ELSE ROUND(numer::numeric / denom, 4)
  END AS new_user_conversions
FROM calendar c
LEFT JOIN LATERAL (
  SELECT
    COUNT(DISTINCT CASE WHEN uf.signed_within_7d = 1 THEN uf.user_id END) AS denom,
    COUNT(DISTINCT CASE WHEN uf.signed_within_7d = 1 AND uf.purchased_within_7d = 1 THEN uf.user_id END) AS numer
  FROM user_flags uf
  WHERE uf.first_event_date BETWEEN DATE_SUB(c.date, INTERVAL 6 DAY) AND c.date
) stats ON TRUE
ORDER BY c.date;