-- How fast do nursing homes fix what surveyors cite? 28.5 days for the harmful ones
-- Fonteum reproducible query — https://fonteum.com/research/nursing-home-deficiency-correction-time
-- Data as of: 2026-05-25  ·  methodology: nh-correction-time/v1
-- Source datasets: CMS Nursing Home Compare
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- Time-to-Correction: CMS Nursing Home Health Deficiencies (2026).
-- Source table: nh_health_deficiencies (CMS Care Compare NH Health
-- Deficiencies; snapshot frozen 2026-05-25; US-Government-Works).
--
-- Metric: correction lead time = correction_date - survey_date, in whole days.
-- Rows excluded: open citations (correction_date IS NULL, n=2,299) and
-- negative spans where correction_date predates survey_date (n=5,126, a CMS
-- data quirk — excluded outright, never clamped to zero). 410,723 of the
-- 415,849 corrected citations carry a valid non-negative span.
--
-- CMS scope/severity bands: no harm A-C, minimal harm D-F, actual harm G-I,
-- immediate jeopardy J-L. "Harm-level" = G+ (G-L).

-- 1. National correction time by harm band -----------------------------------
with corrected as (
  select
    (correction_date - survey_date) as days,
    upper(left(scope_severity_code, 1)) as sev
  from nh_health_deficiencies
  where survey_date is not null
    and correction_date is not null
    and (correction_date - survey_date) >= 0
)
select
  case
    when sev in ('A','B','C') then '1 no harm (A-C)'
    when sev in ('D','E','F') then '2 minimal harm (D-F)'
    when sev in ('G','H','I') then '3 actual harm (G-I)'
    when sev in ('J','K','L') then '4 immediate jeopardy (J-L)'
  end as harm_band,
  count(*) as n,
  round(avg(days)::numeric, 2) as mean_days,
  round(percentile_cont(0.5) within group (order by days)::numeric, 1) as median_days,
  round(percentile_cont(0.9) within group (order by days)::numeric, 1) as p90_days
from corrected
where sev in ('A','B','C','D','E','F','G','H','I','J','K','L')
group by harm_band
order by harm_band;

-- 2. Headline metric: harm-level (Severity G+) correction time ---------------
--    Expected: n=20,297 · mean 28.48 · median 26 · p90 53.
select
  count(*) as harm_level_n,
  round(avg(correction_date - survey_date)::numeric, 2) as mean_days,
  round(percentile_cont(0.5) within group (order by (correction_date - survey_date))::numeric, 1) as median_days,
  round(percentile_cont(0.9) within group (order by (correction_date - survey_date))::numeric, 1) as p90_days
from nh_health_deficiencies
where survey_date is not null
  and correction_date is not null
  and (correction_date - survey_date) >= 0
  and upper(left(scope_severity_code, 1)) in ('G','H','I','J','K','L');

-- 3. State rankings: mean correction time (all corrected, n>=500) ------------
select
  state,
  count(*) as n,
  round(avg(correction_date - survey_date)::numeric, 2) as mean_days,
  round(percentile_cont(0.5) within group (order by (correction_date - survey_date))::numeric, 1) as median_days
from nh_health_deficiencies
where survey_date is not null
  and correction_date is not null
  and (correction_date - survey_date) >= 0
  and state is not null
  and length(state) = 2
group by state
having count(*) >= 500
order by mean_days asc;
