-- A March spike in Medicare enrollment deactivations thinned provider supply in shortage areas
-- Fonteum reproducible query — https://fonteum.com/research/medicare-deactivation-spike
-- Data as of: 2026-04-15  ·  methodology: access-supply/v1
-- Source datasets: CMS PECOS, HRSA HPSA
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- A March spike in Medicare enrollment deactivations thinned provider supply
-- in shortage areas. Snapshot: cms-pecos/2026-04 (pecos_snapshot,
-- snapshot_date = 2026-04-15). Maps deactivations to HRSA HPSA shortage
-- designations by county.
--
-- Each query is annotated with its expected result, so the three headline
-- figures (March +28% above trend; shortage areas +41% vs +19% elsewhere)
-- regenerate from the committed snapshot. "Growth against trend" = the month's
-- deactivation count versus the trailing-twelve-month monthly average
-- (2025-03 .. 2026-02). No absolute monthly counts are hard-coded — the queries
-- return the ratios directly.

-- ── (1) The national spike — March 2026 vs the trailing-12-month average ──────
with monthly as (
  select
    date_trunc('month', deactivation_date)::date as mo,
    count(*)                                      as n
  from pecos_snapshot
  where dataset_id = 'cms-pecos'
    and snapshot_date = date '2026-04-15'
    and deactivation_date >= date '2025-03-01'   -- trailing 12 mo + March 2026
    and deactivation_date <  date '2026-04-01'
  group by 1
)
select
  round(100.0 * (
    max(n) filter (where mo = date '2026-03-01')
    - avg(n) filter (where mo < date '2026-03-01')
  ) / avg(n) filter (where mo < date '2026-03-01'), 0) as march_pct_above_ttm
from monthly;
-- march_pct_above_ttm = 28   (March 2026 deactivations ran 28% above the
--                             trailing-twelve-month monthly average)

-- ── (2) The spike was not uniform — split by HRSA HPSA shortage status ────────
-- Growth against trend, March 2026 vs the trailing-12-month monthly average,
-- for deactivations in HRSA-designated shortage-area counties vs the rest.
with hpsa as (
  select county_fips, bool_or(is_designated) as shortage_area
  from hrsa_hpsa_snapshot
  group by county_fips
),
monthly as (
  select
    coalesce(h.shortage_area, false)                  as shortage_area,
    date_trunc('month', d.deactivation_date)::date    as mo,
    count(*)                                          as n
  from pecos_snapshot d
  left join hpsa h using (county_fips)
  where d.dataset_id = 'cms-pecos'
    and d.snapshot_date = date '2026-04-15'
    and d.deactivation_date >= date '2025-03-01'
    and d.deactivation_date <  date '2026-04-01'
  group by 1, 2
)
select
  shortage_area,
  round(100.0 * (
    max(n) filter (where mo = date '2026-03-01')
    - avg(n) filter (where mo < date '2026-03-01')
  ) / avg(n) filter (where mo < date '2026-03-01'), 0) as march_pct_above_ttm
from monthly
group by shortage_area
order by shortage_area desc;
-- shortage_area = true   -> march_pct_above_ttm = 41   (shortage-area counties)
-- shortage_area = false  -> march_pct_above_ttm = 19   (non-shortage counties)

-- ── (3) March 2026 deactivations by shortage status — raw reconciliation ──────
-- Supporting cut: the March-only deactivation counts and average HPSA score
-- behind the split above. Deactivations concentrated in already-designated
-- shortage areas, the places with the least slack to absorb a departure.
with hpsa as (
  select county_fips,
         max(hpsa_score)        as hpsa_score,
         bool_or(is_designated) as shortage_area
  from hrsa_hpsa_snapshot
  group by county_fips
)
select
  coalesce(h.shortage_area, false) as shortage_area,
  count(*)                         as march_deactivations,
  round(avg(h.hpsa_score), 1)      as avg_hpsa_score
from pecos_snapshot d
left join hpsa h using (county_fips)
where d.dataset_id = 'cms-pecos'
  and d.snapshot_date = date '2026-04-15'
  and d.deactivation_date >= date '2026-03-01'
  and d.deactivation_date <  date '2026-04-01'
group by 1
order by 1 desc;
