Diversity, Inclusion, and Belonging Questions

WITH app_latest AS (
  -- keep latest application per candidate if multiple; change to earliest if preferred
  SELECT DISTINCT ON (candidate_id) *
  FROM applications
  ORDER BY candidate_id, applied_at DESC
),
events AS (
  SELECT
    a.application_id,
    a.candidate_id,
    a.applied_at,
    MIN(i.scheduled_at)      AS first_interview_at,
    MIN(o.offered_at)        FILTER (WHERE o.status <> 'withdrawn') AS first_valid_offer_at,
    h.hired_at
  FROM app_latest a
  LEFT JOIN interviews i ON i.application_id = a.application_id
  LEFT JOIN offers o ON o.application_id = a.application_id
  LEFT JOIN hires h ON h.application_id = a.application_id
  GROUP BY a.application_id, a.candidate_id, a.applied_at, h.hired_at
),
ttt AS (
  SELECT
    e.application_id,
    c.demographic_group, -- e.g., gender, ethnicity, age_bucket
    e.applied_at,
    e.hired_at,
    EXTRACT(EPOCH FROM (e.hired_at - e.applied_at))/86400.0 AS days_to_hire
  FROM events e
  JOIN candidates c ON c.candidate_id = e.candidate_id
  WHERE e.applied_at IS NOT NULL
    AND e.hired_at IS NOT NULL  -- require complete funnel; drop or mark otherwise
)
SELECT
  demographic_group,
  COUNT(*) AS hires,
  ROUND(AVG(days_to_hire),2) AS avg_days_to_hire,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_hire) AS median_days_to_hire
FROM ttt
GROUP BY demographic_group
ORDER BY avg_days_to_hire;

-- 1) cohorted hires
WITH hires_cohort AS (
  SELECT
    employee_id,
    DATE_TRUNC('month', hire_date) AS cohort_month,
    race
  FROM hires
  WHERE hire_date IS NOT NULL
),

-- 2) months 0..11
months AS (
  SELECT 0 AS offset
  UNION ALL
  SELECT offset + 1 FROM months WHERE offset < 11
),

-- 3) expand hires to 12 months observations
cohort_months AS (
  SELECT
    h.employee_id,
    h.race,
    h.cohort_month,
    DATE_ADD('month', m.offset, h.cohort_month) AS obs_month,
    m.offset
  FROM hires_cohort h
  CROSS JOIN months m
),

-- 4) mark active if there is a snapshot for that employee on the obs_month date
-- assume employees_active_snapshot.date stores dates; we compare by month
snap_months AS (
  SELECT
    DATE_TRUNC('month', date) AS snapshot_month,
    employee_id
  FROM employees_active_snapshot
),

activity AS (
  SELECT
    c.cohort_month,
    c.race,
    c.offset,
    c.obs_month,
    c.employee_id,
    CASE WHEN s.employee_id IS NOT NULL THEN 1 ELSE 0 END AS is_active
  FROM cohort_months c
  LEFT JOIN snap_months s
    ON s.employee_id = c.employee_id
    AND s.snapshot_month = c.obs_month
),

-- 5) aggregate to retention rates
agg AS (
  SELECT
    cohort_month,
    race,
    offset AS month_number, -- 0 = hire month, 1 = next month, ...
    COUNT(DISTINCT employee_id) FILTER (WHERE offset = 0) OVER (PARTITION BY cohort_month, race) AS cohort_size,
    COUNT(DISTINCT employee_id) FILTER (WHERE is_active = 1) AS active_count
  FROM activity
  GROUP BY cohort_month, race, offset
)

SELECT
  cohort_month,
  race,
  month_number,
  cohort_size,
  active_count,
  CASE WHEN cohort_size = 0 THEN NULL
       ELSE ROUND( (active_count::decimal / cohort_size) * 100, 2) END AS retention_pct
FROM agg
ORDER BY cohort_month, race, month_number;