Statistical Rigor & Avoiding Common Pitfalls Questions

import numpy as np
import pandas as pd

def bootstrap_median_ci(user_revenue, n_boot=10000, ci=95, seed=42):
    """
    Return median and percentile bootstrap CI for median revenue per user.
    """
    # clean input
    arr = np.asarray(pd.Series(user_revenue).dropna())
    if arr.size == 0:
        raise ValueError("No data after dropping NA")
    rng = np.random.default_rng(seed)
    n = arr.size
    medians = np.empty(n_boot)
    for i in range(n_boot):
        sample_idx = rng.integers(0, n, n)   # sample with replacement
        medians[i] = np.median(arr[sample_idx])
    alpha = (100 - ci) / 2
    lower, upper = np.percentile(medians, [alpha, 100 - alpha])
    return {
        "observed_median": float(np.median(arr)),
        "ci_lower": float(lower),
        "ci_upper": float(upper),
        "n_boot": n_boot,
        "seed": seed
    }

# example
# res = bootstrap_median_ci(df['user_revenue'], n_boot=10000, seed=123)

WITH user_cohort AS (
  -- cohort = first event date per user
  SELECT
    user_id,
    MIN(event_date)::date AS first_event_date
  FROM events
  GROUP BY user_id
),
cohort_size AS (
  -- number of users in each cohort
  SELECT
    first_event_date,
    COUNT(*)::bigint AS cohort_n
  FROM user_cohort
  GROUP BY first_event_date
),
cohort_day_events AS (
  -- aggregate conversions by cohort and day
  SELECT
    uc.first_event_date,
    e.event_date::date AS day,
    SUM(e.conversion_flag)::bigint AS conversions
  FROM events e
  JOIN user_cohort uc ON e.user_id = uc.user_id
  GROUP BY uc.first_event_date, e.event_date::date
),
-- ensure we have a row for every day for each cohort (optional but useful for continuous time series)
cohort_calendar AS (
  SELECT
    c.first_event_date,
    gs::date AS day
  FROM cohort_size c
  JOIN LATERAL (
    SELECT generate_series(c.first_event_date,
                           c.first_event_date + INTERVAL '90 days',  -- adjust horizon as needed
                           INTERVAL '1 day'
    ) AS gs
  ) g ON true
)
SELECT
  cc.first_event_date AS cohort_start,
  cc.day,
  COALESCE(cde.conversions, 0) AS daily_conversions,
  -- 7-day rolling conversions (current day and 6 days before)
  SUM(COALESCE(cde.conversions, 0)) OVER (
    PARTITION BY cc.first_event_date
    ORDER BY cc.day
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS rolling_7d_conversions,
  cs.cohort_n,
  -- conversion rate = rolling conversions / cohort size
  (SUM(COALESCE(cde.conversions, 0)) OVER (
    PARTITION BY cc.first_event_date
    ORDER BY cc.day
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  )::double precision / cs.cohort_n) AS rolling_7d_conv_rate,
  -- standard error for proportion p over n: sqrt(p*(1-p)/n)
  CASE
    WHEN cs.cohort_n > 0 THEN
      sqrt(
        ( (SUM(COALESCE(cde.conversions, 0)) OVER (
            PARTITION BY cc.first_event_date
            ORDER BY cc.day
            RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
          )::double precision / cs.cohort_n)
        * (1 - (SUM(COALESCE(cde.conversions, 0)) OVER (
            PARTITION BY cc.first_event_date
            ORDER BY cc.day
            RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
          )::double precision / cs.cohort_n))
        ) / cs.cohort_n
      )
    ELSE NULL
  END AS rolling_7d_se
FROM cohort_calendar cc
LEFT JOIN cohort_day_events cde
  ON cde.first_event_date = cc.first_event_date AND cde.day = cc.day
JOIN cohort_size cs
  ON cs.first_event_date = cc.first_event_date
ORDER BY cc.first_event_date, cc.day;