-- Why 14% of skilled nursing facilities had a quality drop in Q1
-- Fonteum reproducible query — https://fonteum.com/research/14-percent-snf-quality-drop
-- Data as of: 2026-05-26  ·  methodology: snf-composite/v1
-- Source datasets: NPPES, CMS Care Compare
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- Why 14% of skilled nursing facilities had a quality drop in Q1 (2026Q1).
-- Snapshot: cms-care-compare/2026-05 (frozen 2026-05-26).
-- Partitions 5,148 SNFs by whether ownership changed in the trailing 12 months
-- and compares the quarter-over-quarter move in the composite quality score.

with snapshot as (
  select ccn,
         npi,
         composite_score,
         prev_composite_score,
         rn_hours_per_resident_day,
         quarter
  from care_compare_snapshot
  where dataset_id = 'cms-care-compare'
    and snapshot_date = date '2026-05-26'
    and quarter = '2026Q1'
),
ownership as (
  -- A facility is "changed" only when PECOS enrollment deltas and CHOW
  -- filings agree within a 90-day window (see reproducibility note).
  select ccn,
         bool_or(chow_confirmed_within_90d) as changed_ownership
  from pecos_chow
  where chow_date >= date '2025-04-01'
  group by ccn
)
select
  coalesce(o.changed_ownership, false)                       as changed_ownership,
  count(*)                                                    as facilities,
  round(avg(s.composite_score - s.prev_composite_score), 3)   as avg_quarterly_delta,
  round(avg(s.rn_hours_per_resident_day), 3)                  as avg_rn_hprd
from snapshot s
left join ownership o using (ccn)
group by 1
order by 1;
-- changed_ownership = false  ->  4,427 facilities · avg_quarterly_delta -0.03
-- changed_ownership = true   ->    721 facilities · avg_quarterly_delta -0.19
-- (pooled: 5,148 facilities · national average delta -0.06, more than 3x the
--  prior eight-quarter ±0.02 drift band. avg_rn_hprd is lower in the
--  changed-ownership cohort — the staffing channel behind the composite move.)
