-- Zero-RN days: how often US nursing homes ran a day with no registered nurse on the floor
-- Fonteum reproducible query — https://fonteum.com/research/nursing-home-zero-rn-days
-- Data as of: 2025-06-30  ·  methodology: pbj-zero-rn/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).

-- Zero-RN days 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).
--   zero-RN day   := Hrs_RN = 0      (RN direct-care job code; this column
--                    EXCLUDES RN administrative and RN director-of-nursing
--                    hours, which PBJ records separately as Hrs_RNadmin and
--                    Hrs_RNDON).
--
-- 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) Headline figures ─────────────────────────────────────────────────────
with day_rn as (
  select
    provnum,
    state,
    work_date,
    mds_census,
    coalesce(hrs_rn, 0) as rn_direct_hours
  from public.pbj_daily_nurse_staffing
  where snapshot_quarter = '2025Q2'
    and mds_census > 0
)
select
  count(*)                                                          as facility_days,        -- 1,322,254
  count(*) filter (where rn_direct_hours = 0)                       as zero_rn_days,          -- 77,542
  round(100.0 * count(*) filter (where rn_direct_hours = 0)
        / count(*), 2)                                              as pct_zero_rn_days,      -- 5.86
  count(distinct provnum)                                           as facilities,            -- 14,537
  count(distinct provnum) filter (where rn_direct_hours = 0)        as facilities_any_zero,   -- 5,062
  round(100.0 * count(distinct provnum) filter (where rn_direct_hours = 0)
        / count(distinct provnum), 1)                               as pct_facilities_any,    -- 34.8
  round(avg(mds_census), 0)                                         as avg_daily_census       -- 85
from day_rn;

-- ── (2) State-level rates ────────────────────────────────────────────────────
-- Two columns: share of resident-days with zero RN direct-care hours, and share
-- of facilities with at least one such day. Among states with >= 20 reporting
-- facilities. Louisiana highest (27.9% of days, 79.8% of facilities); Rhode
-- Island lowest (0.2% / 7.0%) — a ~120x spread on the day rate.
with day_rn as (
  select provnum, state, coalesce(hrs_rn, 0) as rn_direct_hours
  from public.pbj_daily_nurse_staffing
  where snapshot_quarter = '2025Q2' and mds_census > 0
)
select
  state,
  round(100.0 * count(*) filter (where rn_direct_hours = 0)
        / count(*), 1)                                              as pct_days_zero_rn,
  round(100.0 * count(distinct provnum) filter (where rn_direct_hours = 0)
        / count(distinct provnum), 1)                               as pct_facilities_zero_rn
from day_rn
group by state
having count(distinct provnum) >= 20
order by pct_days_zero_rn desc;
--  LA 27.9 / 79.8 · OK 25.4 / 80.3 · AR 20.8 / 78.7 · TX 14.2 / 62.2
--  MO 11.8 / 62.2 · GA  8.7 / 51.4 · KS  8.0 / 52.2 · OR  7.5 / 55.6
--  ... (lowest) FL 0.9 / 11.5 · CO 0.9 / 10.7 · MD 0.8 / 12.3
--               NH 0.7 / 18.9 · RI 0.2 / 7.0

-- ── (3) Per-facility distribution of zero-RN days across the 91-day quarter ───
with per_fac as (
  select
    provnum,
    count(*)                                            as resident_days,
    count(*) filter (where coalesce(hrs_rn, 0) = 0)     as zero_days
  from public.pbj_daily_nurse_staffing
  where snapshot_quarter = '2025Q2' and mds_census > 0
  group by provnum
)
select
  count(*) filter (where zero_days = 0)                  as fac_none,        -- 9,475 (65.2%)
  count(*) filter (where zero_days between 1 and 6)      as fac_1_6,         -- 2,650 (18.2%)
  count(*) filter (where zero_days between 7 and 29)     as fac_7_29,        -- 1,497 (10.3%)
  count(*) filter (where zero_days between 30 and 90)    as fac_30_90,       --   864 (5.9%)
  count(*) filter (where zero_days >= 91)                as fac_every_day,   --    51 (0.4%)
  -- Among facilities with ANY zero-RN day: mean count, and how many ran a
  -- zero-RN day on at least half of their resident-days.
  round(avg(zero_days) filter (where zero_days > 0), 1)  as avg_zero_days_among_any, -- 15.3
  count(*) filter (where zero_days > 0
                   and zero_days >= 0.5 * resident_days) as fac_half_or_more         -- 536
from per_fac;

-- ── (4) Direct care vs the whole RN department ───────────────────────────────
-- The headline isolates the RN DIRECT-CARE column. Widening to require zero RN
-- hours across ALL three RN job codes (direct care + administrative + director
-- of nursing) collapses the count: most zero-direct-care days still had an RN
-- in the building, booked to administration or department management.
with day_rn as (
  select
    provnum,
    coalesce(hrs_rn, 0) + coalesce(hrs_rnadmin, 0)
      + coalesce(hrs_rndon, 0)                          as rn_all_codes_hours
  from public.pbj_daily_nurse_staffing
  where snapshot_quarter = '2025Q2' and mds_census > 0
)
select
  count(*) filter (where rn_all_codes_hours = 0)                    as all_rn_zero_days,      -- 7,399
  round(100.0 * count(*) filter (where rn_all_codes_hours = 0)
        / count(*), 2)                                              as pct_all_rn_zero,       -- 0.56
  count(distinct provnum) filter (where rn_all_codes_hours = 0)     as facilities_all_rn_zero -- 1,373
from day_rn;

-- ── (5) Weekday vs weekend zero-RN-direct-care rate ──────────────────────────
-- Counterintuitive: weekdays are WORSE than weekends for zero RN DIRECT-CARE
-- days, because the on-site RN is more often coded to administrative / DON
-- duties Monday–Friday. A bookkeeping pattern, not a clinical one.
with day_rn as (
  select
    extract(isodow from work_date) in (6, 7)            as is_weekend,
    coalesce(hrs_rn, 0)                                 as rn_direct_hours
  from public.pbj_daily_nurse_staffing
  where snapshot_quarter = '2025Q2' and mds_census > 0
)
select
  is_weekend,
  round(100.0 * count(*) filter (where rn_direct_hours = 0)
        / count(*), 2)                                              as pct_zero_rn_days
from day_rn
group by is_weekend
order by is_weekend;
--  is_weekend = false (weekday) -> 6.51
--  is_weekend = true  (weekend) -> 4.26

-- ── (6) Nurse staffing hours per resident-day (national) ──────────────────────
-- Hours-per-resident-day = sum(hours) / sum(census), the standard PBJ HPRD.
select
  round(sum(coalesce(hrs_rn, 0)) / sum(mds_census), 2)              as rn_direct_hprd,  -- 0.43
  round(sum(coalesce(hrs_rn, 0) + coalesce(hrs_rnadmin, 0)
        + coalesce(hrs_rndon, 0)) / sum(mds_census), 2)             as rn_all_hprd,     -- 0.62
  round(sum(coalesce(hrs_rn, 0) + coalesce(hrs_rnadmin, 0)
        + coalesce(hrs_rndon, 0) + coalesce(hrs_lpn, 0)
        + coalesce(hrs_lpnadmin, 0) + coalesce(hrs_cna, 0)
        + coalesce(hrs_natrn, 0) + coalesce(hrs_medaide, 0))
        / sum(mds_census), 2)                                       as total_nurse_hprd -- 3.70
from public.pbj_daily_nurse_staffing
where snapshot_quarter = '2025Q2' and mds_census > 0;

-- ── (7) Contract / agency share of RN hours ──────────────────────────────────
-- PBJ splits each job code into employee vs contract columns. Agency reliance is
-- a recognized marker of staffing instability; it does not, on its own, drive
-- the zero-RN-day count.
select
  round(100.0 * sum(coalesce(hrs_rn_ctr, 0))
        / nullif(sum(coalesce(hrs_rn_emp, 0)
                     + coalesce(hrs_rn_ctr, 0)), 0), 1)             as pct_rn_hours_contract -- 7.3
from public.pbj_daily_nurse_staffing
where snapshot_quarter = '2025Q2' and mds_census > 0;
