-- The agency-staffing line: how much of US nursing-home nurse care now comes from temp staff
-- Fonteum reproducible query — https://fonteum.com/research/nursing-home-agency-staffing-share-2025
-- Data as of: 2025-06-30  ·  methodology: pbj-agency-share/v1
-- Source datasets: CMS Payroll-Based Journal
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- Agency / contract share of nurse staffing in US nursing homes (CMS PBJ, CY2025 Q2).
-- Snapshot: pbj_daily_nurse_staffing, snapshot_quarter = '2025Q2'
--           (the immutable CMS Payroll-Based Journal quarter covering
--            2025-04-01 .. 2025-06-30, downloaded from data.cms.gov).
--
-- Definitions (locked):
--   facility-day   := one nursing home on one calendar day.
--   included       := mds_census > 0  (residents present that day).
--   total nurse hrs:= sum of all eight nurse job-code columns:
--                       RN director  (hrs_rndon),  RN admin (hrs_rnadmin),
--                       RN direct    (hrs_rn),      LPN admin (hrs_lpnadmin),
--                       LPN direct   (hrs_lpn),     CNA (hrs_cna),
--                       aide-in-train(hrs_natrn),   med aide (hrs_medaide).
--   agency hrs     := the matching '_ctr' (contract) column for each of the
--                     eight job codes. PBJ splits every code into _emp
--                     (facility employees) and _ctr (contract / agency staff).
--   agency share   := agency hrs / total nurse hrs.
--
-- Re-running these queries against the 2025Q2 snapshot reproduces every headline
-- figure in the study, exactly. Each query is annotated with its expected result.

-- ── (1) National headline ────────────────────────────────────────────────────
with base as (
  select
    (coalesce(hrs_rn,0)+coalesce(hrs_rndon,0)+coalesce(hrs_rnadmin,0)
     +coalesce(hrs_lpn,0)+coalesce(hrs_lpnadmin,0)
     +coalesce(hrs_cna,0)+coalesce(hrs_natrn,0)+coalesce(hrs_medaide,0)) as tot,
    (coalesce(hrs_rn_ctr,0)+coalesce(hrs_rndon_ctr,0)+coalesce(hrs_rnadmin_ctr,0)
     +coalesce(hrs_lpn_ctr,0)+coalesce(hrs_lpnadmin_ctr,0)
     +coalesce(hrs_cna_ctr,0)+coalesce(hrs_natrn_ctr,0)+coalesce(hrs_medaide_ctr,0)) as ctr,
    mds_census, provnum
  from public.pbj_daily_nurse_staffing
  where snapshot_quarter = '2025Q2' and mds_census > 0
)
select
  count(*)                                       as facility_days,   -- 1,322,254
  count(distinct provnum)                        as facilities,      --    14,537
  round(sum(tot))                                as total_hours,     -- 423,690,623
  round(sum(ctr))                                as agency_hours,    --  25,337,782
  round(100.0*sum(ctr)/sum(tot), 2)              as agency_pct,      --       5.98
  round(sum(ctr)/sum(mds_census), 3)             as agency_hprd,     --      0.226
  round(sum(tot)/sum(mds_census), 2)             as total_hprd       --       3.78
from base;

-- ── (2) Agency share by nurse role ───────────────────────────────────────────
-- LPN most agency-dependent (7.37%), RN next (5.78%), aides lowest (5.51%).
select
  round(100.0*sum(coalesce(hrs_rn_ctr,0)+coalesce(hrs_rndon_ctr,0)+coalesce(hrs_rnadmin_ctr,0))
        / nullif(sum(coalesce(hrs_rn,0)+coalesce(hrs_rndon,0)+coalesce(hrs_rnadmin,0)),0), 2) as rn_agency_pct,   -- 5.78
  round(100.0*sum(coalesce(hrs_lpn_ctr,0)+coalesce(hrs_lpnadmin_ctr,0))
        / nullif(sum(coalesce(hrs_lpn,0)+coalesce(hrs_lpnadmin,0)),0), 2)                     as lpn_agency_pct,  -- 7.37
  round(100.0*sum(coalesce(hrs_cna_ctr,0)+coalesce(hrs_natrn_ctr,0)+coalesce(hrs_medaide_ctr,0))
        / nullif(sum(coalesce(hrs_cna,0)+coalesce(hrs_natrn,0)+coalesce(hrs_medaide,0)),0), 2) as aide_agency_pct -- 5.51
from public.pbj_daily_nurse_staffing
where snapshot_quarter = '2025Q2' and mds_census > 0;

-- ── (3) State agency share + share of the national agency bill ────────────────
-- Intensity (agency share) vs volume (share of all national agency hours).
-- Vermont highest intensity (25.13%) but only 0.9% of national agency hours;
-- New York 10.79% intensity but 13.8% of the national bill. NY+PA+NJ = 32.7%.
-- Alabama lowest intensity (1.01%). Spread VT/AL ~= 25x.
with s as (
  select
    state,
    sum(coalesce(hrs_rn,0)+coalesce(hrs_rndon,0)+coalesce(hrs_rnadmin,0)
        +coalesce(hrs_lpn,0)+coalesce(hrs_lpnadmin,0)
        +coalesce(hrs_cna,0)+coalesce(hrs_natrn,0)+coalesce(hrs_medaide,0)) as tot,
    sum(coalesce(hrs_rn_ctr,0)+coalesce(hrs_rndon_ctr,0)+coalesce(hrs_rnadmin_ctr,0)
        +coalesce(hrs_lpn_ctr,0)+coalesce(hrs_lpnadmin_ctr,0)
        +coalesce(hrs_cna_ctr,0)+coalesce(hrs_natrn_ctr,0)+coalesce(hrs_medaide_ctr,0)) as ctr,
    count(distinct provnum) as facilities
  from public.pbj_daily_nurse_staffing
  where snapshot_quarter = '2025Q2' and mds_census > 0
  group by state
)
select
  state,
  facilities,
  round(100.0*ctr/nullif(tot,0), 2)                       as agency_share_pct,
  round(100.0*ctr/(select sum(ctr) from s), 1)            as share_of_national_agency_pct
from s
where tot > 0
order by agency_share_pct desc;
--  highest: VT 25.13 / 0.9 · PA 13.40 / 12.0 · NH 13.36 / 1.1 · NM 12.73 / 0.9
--           AK 12.53 / 0.2 · NJ 12.46 / 6.9 · ND 12.44 / 0.9 · ME 10.91 / 0.8
--           NY 10.79 / 13.8
--  lowest:  AL 1.01 · FL 1.53 · AR 1.89 · NV 2.46 · TX 2.55

-- ── (4) Per-facility distribution of agency reliance ──────────────────────────
-- 41.5% of homes bought ZERO agency hours; the reliance that exists concentrates
-- in a small tail. 854 homes (>=25%) carry 41.5% of all agency hours; 86 homes
-- ran majority-agency (>=50%); 19 topped 75%.
with f as (
  select
    provnum,
    sum(coalesce(hrs_rn,0)+coalesce(hrs_rndon,0)+coalesce(hrs_rnadmin,0)
        +coalesce(hrs_lpn,0)+coalesce(hrs_lpnadmin,0)
        +coalesce(hrs_cna,0)+coalesce(hrs_natrn,0)+coalesce(hrs_medaide,0)) as tot,
    sum(coalesce(hrs_rn_ctr,0)+coalesce(hrs_rndon_ctr,0)+coalesce(hrs_rnadmin_ctr,0)
        +coalesce(hrs_lpn_ctr,0)+coalesce(hrs_lpnadmin_ctr,0)
        +coalesce(hrs_cna_ctr,0)+coalesce(hrs_natrn_ctr,0)+coalesce(hrs_medaide_ctr,0)) as ctr
  from public.pbj_daily_nurse_staffing
  where snapshot_quarter = '2025Q2' and mds_census > 0
  group by provnum
), p as (
  select provnum, tot, ctr, 100.0*ctr/nullif(tot,0) as pct from f where tot > 0
)
select
  count(*)                                                as facilities,         -- 14,537
  count(*) filter (where ctr = 0)                         as zero_agency,        --  6,037 (41.5%)
  count(*) filter (where pct > 0 and pct < 25)            as gt0_lt25,           --  7,646 (52.6%)
  count(*) filter (where pct >= 25 and pct < 50)          as ge25_lt50,          --    768 (5.3%)
  count(*) filter (where pct >= 50 and pct < 75)          as ge50_lt75,          --     67 (0.5%)
  count(*) filter (where pct >= 75)                       as ge75,               --     19 (0.1%)
  count(*) filter (where pct >= 25)                       as ge25_total,         --    854 (5.9%)
  count(*) filter (where pct >= 50)                       as majority_agency,    --     86
  round(percentile_cont(0.5) within group (order by pct)::numeric, 2) as median_pct, -- 0.50
  -- Share of ALL national agency hours delivered by the >=25%-reliant tail.
  round((select 100.0*sum(ctr) from p where pct >= 25)
        / (select sum(ctr) from p), 1)                    as ge25_share_of_agency_hrs -- 41.5
from p;

-- ── (5) Agency share of RN DIRECT-CARE hours only ─────────────────────────────
-- The narrower denominator referenced in the body (and the headline of the
-- companion zero-RN study): contract share of the RN direct-care job code alone
-- (hrs_rn), excluding RN admin + director-of-nursing. Higher than the all-RN
-- (5.78%) and all-workforce (5.98%) figures because the licensed bedside roles
-- are the hardest to staff permanently.
select
  round(100.0*sum(coalesce(hrs_rn_ctr,0))
        / nullif(sum(coalesce(hrs_rn,0)), 0), 2)          as rn_directcare_agency_pct -- 7.28
from public.pbj_daily_nurse_staffing
where snapshot_quarter = '2025Q2' and mds_census > 0;
