Data Investigation and Root Cause Analysis Questions

WITH clicks AS (
  SELECT user_id, click_time
  FROM ad_clicks
  WHERE campaign_id = 'CAMPAIGN_X'
),
signups AS (
  SELECT user_id, MIN(event_time) AS signup_time
  FROM user_events
  WHERE event = 'signup'
  GROUP BY user_id
),
attribution AS (
  SELECT s.user_id, s.signup_time,
         MAX(c.click_time) AS last_click_time
  FROM signups s
  LEFT JOIN clicks c
    ON c.user_id = s.user_id
    AND c.click_time <= s.signup_time
    AND c.click_time >= s.signup_time - INTERVAL '7 days'
  GROUP BY s.user_id, s.signup_time
)
SELECT
  COUNT(*) FILTER (WHERE last_click_time IS NOT NULL) AS attributed_signups,
  COUNT(*) AS total_signups,
  ROUND(100.0 * COUNT(*) FILTER (WHERE last_click_time IS NOT NULL) / COUNT(*),2) AS pct_attributed
FROM attribution
WHERE signup_time BETWEEN '2025-10-01' AND '2025-10-21';

-- Parameters: :tz (e.g. 'UTC'), :week_start (default 'week'), :min_cohort_size (e.g. 30)
WITH users_cte AS (
  SELECT
    id,
    -- cohort week anchored to timezone; week starts per SQL DATE_TRUNC (typically Monday)
    date_trunc('week', signup_at AT TIME ZONE :tz) AS signup_week
  FROM users
),
weeks AS (
  -- week offsets 0..11
  SELECT generate_series(0,11) AS wk
),
cohort_users AS (
  -- cohort sizes
  SELECT signup_week, count(*) AS cohort_size
  FROM users_cte
  GROUP BY signup_week
),
user_week_events AS (
  -- for each user and candidate week offset, determine if they were active in that week
  SELECT
    u.id AS user_id,
    u.signup_week,
    w.wk,
    -- start and end bounds for the target week in cohort timezone
    (u.signup_week + (w.wk * interval '1 week'))                      AS week_start,
    (u.signup_week + ((w.wk + 1) * interval '1 week'))                AS week_end,
    CASE WHEN EXISTS (
      SELECT 1 FROM events e
      WHERE e.user_id = u.id
        AND (e.occurred_at AT TIME ZONE :tz) >= (u.signup_week + (w.wk * interval '1 week'))
        AND (e.occurred_at AT TIME ZONE :tz) <  (u.signup_week + ((w.wk + 1) * interval '1 week'))
    ) THEN 1 ELSE 0 END AS active
  FROM users_cte u
  CROSS JOIN weeks w
),
agg AS (
  SELECT
    uwe.signup_week,
    uwe.wk,
    sum(uwe.active) AS active_users,
    c.cohort_size
  FROM user_week_events uwe
  JOIN cohort_users c USING (signup_week)
  GROUP BY 1,2,4
)
SELECT
  signup_week,
  wk AS week_offset,
  cohort_size,
  active_users,
  -- percentage retained
  ROUND(100.0 * active_users::numeric / cohort_size, 2) AS pct_retained,
  -- mask small cohorts: return NULL if cohort too small
  CASE WHEN cohort_size < :min_cohort_size THEN NULL
       ELSE ROUND(100.0 * active_users::numeric / cohort_size, 2) END AS pct_retained_masked
FROM agg
ORDER BY signup_week DESC, wk;