Tenure & Retention — Cohort Survival Analysis

Survival analysis tracks what percentage of a hire cohort is still employed at 6, 12, 18, 24 months. This reveals retention cliffs and whether recent cohorts are retaining better or worse than older ones.

-- Cohort survival: % still employed at 6, 12, 18, 24 months
WITH cohorts AS (
    SELECT
        DATE_TRUNC('year', hire_date)::DATE     AS cohort_year,
        id,
        hire_date,
        termination_date
    FROM hr_employees
    WHERE hire_date >= CURRENT_DATE - INTERVAL '5 years'
),
survival AS (
    SELECT
        cohort_year,
        COUNT(*)                                                    AS cohort_size,
        COUNT(*) FILTER (
            WHERE termination_date IS NULL
               OR termination_date >= hire_date + INTERVAL '6 months'
        )                                                           AS still_at_6m,
        COUNT(*) FILTER (
            WHERE termination_date IS NULL
               OR termination_date >= hire_date + INTERVAL '12 months'
        )                                                           AS still_at_12m,
        COUNT(*) FILTER (
            WHERE termination_date IS NULL
               OR termination_date >= hire_date + INTERVAL '18 months'
        )                                                           AS still_at_18m,
        COUNT(*) FILTER (
            WHERE termination_date IS NULL
               OR termination_date >= hire_date + INTERVAL '24 months'
        )                                                           AS still_at_24m
    FROM cohorts
    GROUP BY cohort_year
)
SELECT
    cohort_year,
    cohort_size,
    ROUND(100.0 * still_at_6m  / cohort_size, 1) AS retention_6m_pct,
    ROUND(100.0 * still_at_12m / cohort_size, 1) AS retention_12m_pct,
    ROUND(100.0 * still_at_18m / cohort_size, 1) AS retention_18m_pct,
    ROUND(100.0 * still_at_24m / cohort_size, 1) AS retention_24m_pct
FROM survival
ORDER BY cohort_year;