-- Who is enrolled in Medicare? The nurse practitioner is now the most common clinician
-- Fonteum reproducible query — https://fonteum.com/research/pecos-enrollment
-- Data as of: 2026-05-28  ·  methodology: pecos-enrollment/v1
-- Source datasets: CMS PECOS
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- Medicare provider enrollment (PECOS), explained — fully reproducible query.
--
-- Source:   CMS PECOS Ordering & Referring / provider-enrollment extract.
-- Snapshot: cms-pecos / federal release 2026-05-28, 2,981,799 enrollment records.
-- Table:    public.pecos_providers (public, read-only).
-- Identity: one row = one Medicare provider-enrollment record, keyed by NPI +
--           provider_type_cd. A single NPI can hold multiple enrollment records
--           (different provider types / states), so records > distinct NPIs.
--
-- Every headline figure in the study resolves to one of the rows below.
WITH totals AS (
  SELECT
    count(*)                                                         AS enrollment_records,
    count(DISTINCT npi)                                             AS distinct_providers,
    count(*) FILTER (WHERE org_name IS NOT NULL AND org_name <> '') AS org_records,
    count(*) FILTER (WHERE last_name IS NOT NULL AND last_name <> '') AS individual_records,
    count(DISTINCT provider_type_desc)                             AS distinct_provider_types,
    count(DISTINCT state_cd)                                       AS jurisdictions,
    max(source_release_date)                                       AS federal_release
  FROM public.pecos_providers
)
SELECT
  t.enrollment_records,                                                    -- 2,981,799
  t.distinct_providers,                                                    -- 2,556,656
  round(t.enrollment_records::numeric / t.distinct_providers, 3)
                                                  AS records_per_provider, -- 1.166
  t.org_records,                                                           -- 433,496
  t.individual_records,                                                    -- 2,548,303
  round(100.0 * t.org_records / t.enrollment_records, 1)
                                                  AS org_pct,              -- 14.5%
  t.distinct_provider_types,                                               -- 325
  t.jurisdictions,                                                         -- 56
  t.federal_release                                                        -- 2026-05-28
FROM totals t;

-- Most-enrolled provider types (top 6 by enrollment-record volume):
SELECT
  provider_type_desc                                        AS provider_type,
  count(*)                                                  AS records,
  round(100.0 * count(*) / sum(count(*)) OVER (), 2)        AS pct
FROM public.pecos_providers
GROUP BY provider_type_desc
ORDER BY records DESC
LIMIT 6;
--  PRACTITIONER - NURSE PRACTITIONER                413,539  13.87   <- single largest type
--  PART B SUPPLIER - CLINIC/GROUP PRACTICE          239,492   8.03   <- largest organizational type
--  PRACTITIONER - PHYSICIAN ASSISTANT               195,488   6.56
--  PRACTITIONER - INTERNAL MEDICINE                 144,202   4.84   <- largest single physician specialty
--  PRACTITIONER - FAMILY PRACTICE                   129,130   4.33
--  PRACTITIONER - PHYSICAL THERAPIST IN PRIVATE...  126,499   4.24

-- Advanced-practice share (NP + PA) and the NP-to-physician ratio:
SELECT
  (SELECT count(*) FROM public.pecos_providers
     WHERE provider_type_desc IN ('PRACTITIONER - NURSE PRACTITIONER',
                                  'PRACTITIONER - PHYSICIAN ASSISTANT'))          AS np_pa_records,   -- 609,027
  round(100.0 * (SELECT count(*) FROM public.pecos_providers
     WHERE provider_type_desc IN ('PRACTITIONER - NURSE PRACTITIONER',
                                  'PRACTITIONER - PHYSICIAN ASSISTANT'))
     / (SELECT count(*) FROM public.pecos_providers), 2)                         AS np_pa_pct,       -- 20.42%
  round(
    (SELECT count(*)::numeric FROM public.pecos_providers
       WHERE provider_type_desc = 'PRACTITIONER - NURSE PRACTITIONER')
    / (SELECT count(*) FROM public.pecos_providers
       WHERE provider_type_desc = 'PRACTITIONER - INTERNAL MEDICINE'), 2)        AS np_to_internal_med_ratio; -- 2.87

-- State concentration (top 5 by enrollment records):
SELECT
  state_cd                                                  AS state,
  count(*)                                                  AS records,
  round(100.0 * count(*) / sum(count(*)) OVER (), 2)        AS pct
FROM public.pecos_providers
GROUP BY state_cd
ORDER BY records DESC
LIMIT 5;
--  CA  262,214  8.79
--  TX  207,328  6.95
--  NY  198,758  6.67
--  FL  185,769  6.23
--  PA  127,899  4.29
