-- The Medicare Opt Out List Is Mostly Behavioral Health
-- Fonteum reproducible query — https://fonteum.com/research/medicare-opt-out-behavioral-health-2026
-- Data as of: 2026-05-15  ·  methodology: cms-opt-out/v1
-- Source datasets: CMS Provider Data Catalog
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- Who opts OUT of Medicare — and why it is overwhelmingly a behavioral-health
-- story. Fully reproducible query.
--
-- Question: of the clinicians who have filed a formal affidavit OPTING OUT of
-- Medicare (agreeing to treat Medicare patients only under private contract,
-- where neither the clinician nor the patient may bill Medicare for the care),
-- what kind of provider are they, where are they, and when did they opt out?
-- The lead figure: 34,155 of the 56,117 affidavits on CMS's published opt-out
-- list — 60.9% — are held by five behavioral-health specialties (clinical
-- psychologist, clinical social worker, mental health counselor, marriage &
-- family therapist, psychiatry). Opting out is a coverage signal, NOT a
-- quality, fraud, or wrongdoing signal of any kind.
--
-- Source:
--   public.cms_opt_out_affidavits — CMS "Opt Out Affidavits" public-use file,
--     published monthly via the CMS data catalog (data.cms.gov, dataset
--     9887a515-…). 56,117 affidavit rows; snapshot last_updated 2026-05-15.
--     Public, read-only. License: US-Government-Works (17 U.S.C. Sec. 105).
--     methodology_version = 'cms-opt-out/v1'.
--
-- Universe: this study reads the published file AS A WHOLE — every row is a
--   clinician CMS lists as having a Medicare opt-out affidavit in effect. The
--   file is the point-in-time snapshot (last_updated 2026-05-15); figures are
--   not modeled as a trend. CMS removes affidavits once withdrawn, so the file
--   is the current opt-out roster, not a cumulative history.
--
-- Counting note: an affidavit carries an effective date and a 2-year end date
--   (since 2015 affidavits auto-renew unless the clinician cancels), so the
--   effective-date YEAR below is the year a clinician FIRST opted out, not a
--   yearly flow of net new opt-outs. 55,568 distinct NPIs across 56,117 rows.
--   No row in this snapshot has a NULL NPI. No individual is named in the study.

-- ============================================================================
-- (1) Universe reconciliation — the published file at a glance.
-- ============================================================================
-- audit-claim: universe
SELECT
  count(*)                                                          AS affidavits,
  count(DISTINCT npi)                                               AS distinct_npi,
  count(DISTINCT specialty)                                         AS specialties,
  count(*) FILTER (WHERE npi IS NULL OR npi = '')                   AS null_npi,
  min(optout_effective_date)                                        AS earliest_effective,
  max(optout_effective_date)                                        AS latest_effective,
  max(last_updated)                                                 AS snapshot
FROM public.cms_opt_out_affidavits;
--  affidavits 56,117 · distinct_npi 55,568 · specialties 116 · null_npi 0
--  earliest_effective 1998-01-01 · latest_effective 2026-07-01 · snapshot 2026-05-15

-- ============================================================================
-- (2) HEADLINE: opt-out by specialty, top 12. Five behavioral-health
--     specialties take the top five slots and 60.9% of the whole file.
-- ============================================================================
-- audit-claim: specialty-vector
SELECT
  specialty,
  count(*)                                                          AS affidavits,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1)                AS pct_of_all
FROM public.cms_opt_out_affidavits
GROUP BY specialty
ORDER BY affidavits DESC
LIMIT 12;
--  Clinical Psychologist          7,730  13.8%
--  Mental Health Counselor        7,507  13.4%
--  Clinical Social Worker         7,311  13.0%
--  Marriage And Family Therapist  6,633  11.8%
--  Psychiatry                     4,974   8.9%
--  Dentist                        4,464   8.0%
--  Oral Surgery                   3,942   7.0%
--  Family Practice                2,808   5.0%
--  Nurse Practitioner             2,685   4.8%
--  Internal Medicine              1,710   3.0%
--  Obstetrics/Gynecology            766   1.4%
--  Physician Assistant              670   1.2%

-- ============================================================================
-- (3) The behavioral-health share, computed directly. The five specialties in
--     (2) are aggregated into one group; dental (dentist + oral/maxillofacial
--     surgery) is the second-largest block.
-- ============================================================================
-- audit-claim: grouped-share
WITH classified AS (
  SELECT
    specialty,
    CASE
      WHEN specialty IN (
        'Clinical Psychologist','Clinical Social Worker','Mental Health Counselor',
        'Marriage And Family Therapist','Psychiatry'
      ) THEN 'behavioral'
      WHEN specialty IN ('Dentist','Oral Surgery','Maxillofacial Surgery')
        THEN 'dental'
      ELSE 'other'
    END AS specialty_group
  FROM public.cms_opt_out_affidavits
)
SELECT
  count(*) FILTER (WHERE specialty_group = 'behavioral')             AS behavioral,
  round(100.0 * count(*) FILTER (WHERE specialty_group = 'behavioral') / count(*), 1)
                                                                     AS behavioral_pct,
  count(*) FILTER (WHERE specialty_group = 'dental')                 AS dental,
  round(100.0 * count(*) FILTER (WHERE specialty_group = 'dental') / count(*), 1)
                                                                     AS dental_pct,
  count(*) FILTER (WHERE specialty_group IN ('behavioral', 'dental')) AS behavioral_dental,
  round(100.0 * count(*) FILTER (
    WHERE specialty_group IN ('behavioral', 'dental')
  ) / count(*), 1)                                                   AS behavioral_dental_pct,
  count(*) FILTER (WHERE specialty_group = 'other')                  AS other_affidavits,
  count(DISTINCT specialty) FILTER (WHERE specialty_group = 'other') AS other_specialties
FROM classified;
--  behavioral 34,155 · behavioral_pct 60.9% · dental 8,823 · dental_pct 15.7%
--  behavioral+dental 42,978 (76.6%) · other 13,139 affidavits across 108 specialties

-- ============================================================================
-- (4) WHEN they opted out — first-opt-out year, 2018 onward. 2024 is a step
--     change: 15,978 affidavits take effect, 5x the 2023 count (3,190), and
--     10,844 of the 2024 cohort (67.9%) are marriage & family therapists or
--     mental health counselors — the two specialties Congress first allowed to
--     bill Medicare effective 2024-01-01 (Consolidated Appropriations Act, 2023).
--     They became eligible to enroll and immediately filed to opt back out.
-- ============================================================================
-- audit-claim: year-vector
WITH by_year AS (
  SELECT
    extract(year FROM optout_effective_date)::int                   AS effective_year,
    count(*)                                                        AS affidavits,
    count(*) FILTER (WHERE specialty IN (
      'Marriage And Family Therapist','Mental Health Counselor'))   AS mft_and_mhc,
    count(*) FILTER (WHERE specialty IN (
      'Clinical Psychologist','Clinical Social Worker','Mental Health Counselor',
      'Marriage And Family Therapist','Psychiatry'))                AS behavioral
  FROM public.cms_opt_out_affidavits
  WHERE optout_effective_date >= DATE '2018-01-01'
  GROUP BY effective_year
)
SELECT
  effective_year,
  affidavits,
  mft_and_mhc,
  behavioral,
  CASE WHEN effective_year = 2024 THEN round(
    affidavits::numeric / (SELECT affidavits FROM by_year WHERE effective_year = 2023), 1
  ) END                                                              AS vs_2023_multiple,
  round(100.0 * behavioral / affidavits, 1)                           AS behavioral_pct,
  round(100.0 * mft_and_mhc / affidavits, 1)                          AS mft_mhc_pct
FROM by_year
ORDER BY effective_year;
--  2018  2,312      2   1,197
--  2019  2,260      0   1,218
--  2020  1,680      0     798
--  2021  2,023      0     981
--  2022  2,449      0   1,145
--  2023  3,190     24   1,777
--  2024 15,978 10,844  14,192 · 5.0x 2023 · 88.8% behavioral · 67.9% MFT/MHC
--  2025  6,770  2,588   4,772
--  2026  2,310    682   1,436   (partial year)

-- ============================================================================
-- (5) WHERE they are — top 10 states by affidavit count, with each state's
--     own behavioral-health share. California alone holds 19.1% of the national
--     opt-out file; Massachusetts has the highest behavioral share (81.7%),
--     Florida the lowest of the large states (36.2%, a more dental/physician mix).
-- ============================================================================
-- audit-claim: state-vector
SELECT
  state_code,
  count(*)                                                          AS affidavits,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1)                AS pct_of_all,
  round(100.0 * count(*) FILTER (WHERE specialty IN (
    'Clinical Psychologist','Clinical Social Worker','Mental Health Counselor',
    'Marriage And Family Therapist','Psychiatry')) / count(*), 1)   AS behavioral_pct
FROM public.cms_opt_out_affidavits
WHERE state_code IS NOT NULL
GROUP BY state_code
ORDER BY affidavits DESC
LIMIT 10;
--  CA 10,723 19.1% bh 76.8% · NY 4,822 8.6% bh 65.7% · TX 3,477 6.2% bh 49.4%
--  WA 2,780 5.0% bh 70.2% · FL 2,716 4.8% bh 36.2% · IL 2,122 3.8% bh 58.1%
--  MA 2,081 3.7% bh 81.7% · NC 1,920 3.4% bh 62.7% · VA 1,884 3.4% bh 57.7%
--  MD 1,852 3.3% bh 70.6%

-- ============================================================================
-- (6) Order/refer eligibility by group. Opting out of billing does NOT strip a
--     clinician of the separate right to ORDER and REFER Medicare services —
--     but that right is tied to provider TYPE. Physicians and most NPPs keep it;
--     psychologists, social workers, counselors and MFTs generally cannot order
--     and refer at all, which is why the behavioral group's eligibility is low.
--     This is a structural artifact of provider role, not a behavioral choice.
-- ============================================================================
-- audit-claim: order-refer-vector
WITH g AS (
  SELECT
    CASE
      WHEN specialty IN ('Clinical Psychologist','Clinical Social Worker',
        'Mental Health Counselor','Marriage And Family Therapist','Psychiatry')
        THEN 'behavioral'
      WHEN specialty IN ('Dentist','Oral Surgery','Maxillofacial Surgery')
        THEN 'dental'
      ELSE 'other'
    END                                                             AS grp,
    eligible_to_order_and_refer                                     AS e
  FROM public.cms_opt_out_affidavits
)
SELECT
  grp,
  count(*)                                                          AS affidavits,
  count(*) FILTER (WHERE e)                                         AS order_refer_eligible,
  round(100.0 * count(*) FILTER (WHERE e) / count(*), 1)           AS eligible_pct
FROM g
GROUP BY grp
ORDER BY affidavits DESC;
--  behavioral 34,155 · 11,106 eligible · 32.5%
--  other      13,139 · 11,166 eligible · 85.0%
--  dental      8,823 ·  7,461 eligible · 84.6%
