Outcomes and Progress Tracking Questions

WITH step_times AS (
  SELECT
    a.user_id,
    MIN(CASE WHEN e.event_name = 'A' AND e.event_time BETWEEN a.created_at AND a.created_at + INTERVAL '14' DAY THEN e.event_time END) AS t_a,
    MIN(CASE WHEN e.event_name = 'B' AND e.event_time BETWEEN a.created_at AND a.created_at + INTERVAL '14' DAY THEN e.event_time END) AS t_b,
    MIN(CASE WHEN e.event_name = 'C' AND e.event_time BETWEEN a.created_at AND a.created_at + INTERVAL '14' DAY THEN e.event_time END) AS t_c
  FROM accounts a
  LEFT JOIN events e
    ON e.user_id = a.user_id
    AND e.event_name IN ('A','B','C')
  GROUP BY a.user_id
)

SELECT
  s.user_id,
  CASE
    WHEN t_a IS NOT NULL AND t_b IS NOT NULL AND t_c IS NOT NULL
         AND t_a < t_b AND t_b < t_c THEN 1
    ELSE 0
  END AS onboarding_completion_flag
FROM step_times s;

-- Overall completion rate
SELECT
  AVG(onboarding_completion_flag::FLOAT) AS completion_rate
FROM (
  SELECT
    CASE
      WHEN t_a IS NOT NULL AND t_b IS NOT NULL AND t_c IS NOT NULL
           AND t_a < t_b AND t_b < t_c THEN 1
      ELSE 0
    END AS onboarding_completion_flag
  FROM step_times
) t;