-- Nursing Home Survey Results: How Fast Deficiencies Get Fixed
-- 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).

-- Audit observation: 2026-07-14. Source snapshot remains 2026-05-25.
-- audit-claim: harm-band-vector
with severity_bands(sev, harm_band) as (
  values
    ('A', '1 no harm (A-C)'), ('B', '1 no harm (A-C)'), ('C', '1 no harm (A-C)'),
    ('D', '2 minimal harm (D-F)'), ('E', '2 minimal harm (D-F)'), ('F', '2 minimal harm (D-F)'),
    ('G', '3 actual harm (G-I)'), ('H', '3 actual harm (G-I)'), ('I', '3 actual harm (G-I)'),
    ('J', '4 immediate jeopardy (J-L)'), ('K', '4 immediate jeopardy (J-L)'), ('L', '4 immediate jeopardy (J-L)')
), 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
  severity_bands.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
join severity_bands using (sev)
group by severity_bands.harm_band
order by severity_bands.harm_band;

-- audit-claim: harm-level-summary
--    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');

-- audit-claim: state-correction-vector
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;

-- audit-claim: all-corrected-summary
select
  count(*) as valid_corrected,
  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;

-- audit-claim: file-reconciliation
select
  count(*) as total_citations,
  count(*) filter (where survey_date is not null and correction_date is not null) as both_dates,
  count(*) filter (where correction_date is null) as open_without_correction,
  count(*) filter (
    where survey_date is not null
      and correction_date is not null
      and correction_date < survey_date
  ) as negative_spans,
  count(*) filter (
    where survey_date is not null
      and correction_date is not null
      and correction_date >= survey_date
  ) as valid_nonnegative_spans,
  count(distinct ccn) as facilities,
  min(survey_date) as earliest_survey_date,
  max(survey_date) as latest_survey_date
from nh_health_deficiencies;
