Healthcare AI Pivot Dataset

-- Track department size changes over time
SELECT 
  date_trunc('month', event_date) as month,
  department,
  count(distinct employee_id) as headcount
FROM events
WHERE event_type IN ('hire', 'job_change', 'termination')
GROUP BY 1, 2
ORDER BY 1, 2;

-- Analyze transitions to AI roles
SELECT 
  e.department as from_dept,
  c.changes->>'department' as to_dept,
  count(*) as transition_count
FROM events e
JOIN changes c ON e.event_id = c.event_id
WHERE e.event_type = 'job_change'
  AND (e.department LIKE '%Engineering%' 
    OR c.changes->>'department' LIKE '%AI%')
GROUP BY 1, 2
ORDER BY 3 DESC;

-- Track leadership role changes
SELECT 
  event_date,
  employee_id,
  changes->>'from_role' as previous_role,
  changes->>'to_role' as new_role
FROM events
WHERE event_type = 'job_change'
  AND (
    changes->>'from_role' LIKE '%Director%'
    OR changes->>'from_role' LIKE '%VP%'
    OR changes->>'to_role' LIKE '%Director%'
    OR changes->>'to_role' LIKE '%VP%'
  )
ORDER BY event_date;

-- Analyze hiring patterns by region
SELECT 
  date_trunc('month', hire_date) as month,
  office_location,
  count(*) as new_hires
FROM employees
WHERE hire_date >= '2023-08-01'  -- Phase 3 start
  AND office_location IN ('London', 'Singapore')
GROUP BY 1, 2
ORDER BY 1, 2;

-- Track team diversity metrics over time
WITH monthly_stats AS (
  SELECT 
    date_trunc('month', event_date) as month,
    department,
    count(distinct case when demographics->>'gender' = 'Woman' then employee_id end) as women,
    count(distinct employee_id) as total
  FROM events
  WHERE event_type IN ('hire', 'job_change', 'termination')
  GROUP BY 1, 2
)
SELECT 
  month,
  department,
  round((women::float / total) * 100, 2) as women_percentage
FROM monthly_stats
ORDER BY 1, 2;