Individual Mentoring and Coaching Questions

WITH mentee_prom AS (
  SELECT m.mentee_id, m.start_date,
         MIN(e.promotion_date) AS first_promo
  FROM mentors_mentees m
  LEFT JOIN employee_snapshot e
    ON e.anon_id = m.mentee_id
   AND e.promotion_date BETWEEN m.start_date AND DATE_ADD(m.start_date, INTERVAL 12 MONTH)
  GROUP BY 1,2
)
SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DATEDIFF(first_promo, start_date)) AS median_days_to_promo,
  COUNT(*) FILTER (WHERE first_promo IS NOT NULL) AS promoted_count,
  COUNT(*) AS mentee_count
FROM mentee_prom;

WITH mentored AS (
  SELECT m.mentee_id, m.start_date,
         CASE WHEN EXISTS (
           SELECT 1 FROM employee_snapshot e
           WHERE e.anon_id = m.mentee_id
             AND e.snapshot_date BETWEEN DATE_ADD(m.start_date, INTERVAL 12 MONTH) AND DATE_ADD(m.start_date, INTERVAL 12 MONTH)
             AND e.active_flag = true
         ) THEN 1 ELSE 0 END AS retained_12m
  FROM mentors_mentees m
),
non_mentored AS (
  -- simple matching by hire_date bucket & level; production: propensity-score matching
  SELECT e.anon_id AS candidate_id, e.hire_date, e.org_level,
    CASE WHEN EXISTS (
      SELECT 1 FROM employee_snapshot s
      WHERE s.anon_id = e.anon_id
        AND s.snapshot_date BETWEEN DATE_ADD(e.hire_date, INTERVAL 12 MONTH) AND DATE_ADD(e.hire_date, INTERVAL 12 MONTH)
        AND s.active_flag = true
    ) THEN 1 ELSE 0 END AS retained_12m
  FROM employee_snapshot e
  WHERE e.snapshot_date = (SELECT MAX(snapshot_date) FROM employee_snapshot)
    AND e.anon_id NOT IN (SELECT mentee_id FROM mentors_mentees)
    -- match filters (level, hire_date bucket) applied here
)
SELECT
  AVG(mentored.retained_12m) AS mentored_retention,
  AVG(non_mentored.retained_12m) AS non_mentored_retention,
  AVG(mentored.retained_12m) - AVG(non_mentored.retained_12m) AS retention_delta
FROM mentored
JOIN (
  SELECT * FROM non_mentored -- in practice use matching to pair cohorts
) nm ON 1=1 LIMIT 1;