Diversity, Inclusion, and Belonging Questions

-- 1. Base employee events: hires, status by quarter, promotions
WITH hires AS (
  SELECT employee_id,
         DATE_TRUNC('quarter', hire_date) AS hire_quarter,
         hire_date
  FROM employees
  WHERE hire_date IS NOT NULL
),
status_by_q AS (
  -- snapshot per quarter: active if not terminated before quarter end
  SELECT e.employee_id,
         h.hire_quarter,
         DATE_TRUNC('quarter', d.quarter_start) AS quarter,
         CASE WHEN (t.termination_date IS NULL OR t.termination_date >= d.quarter_end) THEN 1 ELSE 0 END AS active
  FROM hires h
  JOIN (
    -- generate quarter ranges covering analysis window
    SELECT generate_series(min(hire_date)::date, current_date, interval '3 month') AS quarter_start
    FROM hires
  ) d ON 1=1
  JOIN employees e ON e.employee_id = h.employee_id
  LEFT JOIN employees t ON t.employee_id = e.employee_id
  WHERE d.quarter_start >= h.hire_quarter
),
promotions AS (
  SELECT employee_id,
         MIN(promotion_date) AS first_promo_date
  FROM promotions_table
  GROUP BY employee_id
)
SELECT h.employee_id, h.hire_quarter, s.quarter,
       DATE_PART('quarter', AGE(s.quarter, h.hire_quarter)) + 4*(DATE_PART('year', s.quarter) - DATE_PART('year', h.hire_quarter)) AS quarters_since_hire,
       s.active,
       p.first_promo_date,
       CASE WHEN p.first_promo_date IS NOT NULL THEN DATE_PART('day', p.first_promo_date - h.hire_date) ELSE NULL END AS days_to_promo
FROM hires h
JOIN status_by_q s ON s.employee_id = h.employee_id
LEFT JOIN promotions p ON p.employee_id = h.employee_id;

WITH cohort_events AS ( ...use previous query as CTE... )
SELECT hire_quarter,
       quarters_since_hire,
       COUNT(*) FILTER (WHERE active=1) AS active_count,
       COUNT(*) AS cohort_size,
       ROUND(100.0 * SUM(active)::numeric / COUNT(*),2) AS retention_pct
FROM cohort_events
GROUP BY hire_quarter, quarters_since_hire
ORDER BY hire_quarter, quarters_since_hire;

SELECT hire_quarter,
       COUNT(first_promo_date) AS promoted_count,
       COUNT(*) AS cohort_size,
       ROUND(100.0 * COUNT(first_promo_date)/COUNT(*),2) AS promotion_rate_pct,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_promo) AS median_days_to_promo
FROM (
  SELECT DISTINCT employee_id, hire_quarter, first_promo_date, days_to_promo
  FROM cohort_events
) t
GROUP BY hire_quarter
ORDER BY hire_quarter;

import numpy as np
import pandas as pd

# reverse 1-5 item
df['q3_rev'] = 6 - df['q3']

# cronbach alpha
def cronbach_alpha(df_items):
    items = df_items.dropna(axis=1, how='all')
    N = items.shape[1]
    item_vars = items.var(axis=0, ddof=1)
    total_var = items.sum(axis=1).var(ddof=1)
    return (N / (N-1)) * (1 - item_vars.sum() / total_var)

alpha = cronbach_alpha(df[['q1','q2','q3_rev','q4']])