-- Where Medicare providers cluster: home health and DME market saturation, 2025
-- Fonteum reproducible query — https://fonteum.com/research/medicare-market-saturation-2026
-- Data as of: 2026-06-15  ·  methodology: market-saturation/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).

-- Medicare provider market saturation as a program-integrity signal —
-- fully reproducible query.
--
-- Question: where do Medicare fee-for-service (FFS) providers and suppliers
-- cluster far past what local beneficiary demand explains? We measure, over
-- the most recent annual reference period, the provider-to-beneficiary ratio
-- (providers per 1,000 FFS beneficiaries) by service category and by county —
-- the same "market saturation" metric CMS itself publishes for program-
-- integrity MONITORING. High saturation is a risk/oversight signal, NOT proof
-- of fraud. The lead figure is Los Angeles County's home health saturation:
-- 1,847 agencies, 2.12 per 1,000 FFS beneficiaries, ~9.9x the national rate.
--
-- Source:
--   public.cms_market_saturation — CMS Market Saturation & Utilization
--     State-County, published via the CMS data catalog (data.cms.gov),
--     Program Integrity > Market Saturation by Type of Service.
--     1,030,290 rows across 15 yearly reference periods (2020-2025), 24 service
--     categories, and three aggregation levels (NATION + TERRITORIES, STATE,
--     COUNTY = 3,141 counties). Snapshot release 2025-12-01; public, read-only.
--     License: US-Government-Works (17 U.S.C. Sec. 105).
--     methodology_version = 'cms-market-saturation/v1'.
--
-- Universe: this study reads the LATEST annual period only
--   (reference_period = '2025-01-01 to 2025-12-31', 68,068 rows) so every
--   figure is point-in-time, not a trend. The period is resolved as
--   max(reference_period) at query time, never hard-coded, so the figures
--   advance when CMS publishes the next file.
--
-- Counting note (CRITICAL): CMS counts a provider in EVERY county where it
--   serves at least one beneficiary, so county provider counts are presences,
--   not distinct firms — the sum of county counts exceeds the national DISTINCT
--   count (home health: 23,311 county presences vs 8,488 distinct agencies).
--   We therefore compare the saturation RATE (providers per 1,000 FFS benes)
--   across geographies, and label raw county counts as "agencies serving the
--   county," never as a share of the national total.

-- ============================================================================
-- (1) National saturation by service category, latest period.
--     providers_per_1k = number_of_providers / FFS beneficiaries * 1000.
--     Home health and DME are the categories CMS singles out for program-
--     integrity monitoring; the national home health rate (0.215) is the
--     baseline the county figures are measured against.
-- ============================================================================
WITH latest AS (SELECT max(reference_period) AS p FROM public.cms_market_saturation)
SELECT
  type_of_service,
  number_of_providers::bigint                                          AS providers,
  number_of_ffs_beneficiaries::bigint                                  AS ffs_benes,
  number_of_users::bigint                                              AS users,
  average_users_per_provider                                           AS users_per_provider,
  total_payment::bigint                                                AS total_payment,
  round(1000.0 * number_of_providers
        / nullif(number_of_ffs_beneficiaries, 0), 3)                   AS providers_per_1k_ffs
FROM public.cms_market_saturation m, latest
WHERE m.aggregation_level = 'NATION + TERRITORIES'
  AND m.reference_period = latest.p
ORDER BY providers_per_1k_ffs DESC NULLS LAST;
--  Home Health             8,488 providers · 39,524,879 benes · 0.215 / 1k · $15.97B
--  Durable Medical Equip. 45,963 providers · 39,598,270 benes · 1.161 / 1k · $27.04B
--  Hospice                 5,024 providers · 39,475,379 benes · 0.127 / 1k · $15.41B
--  Skilled Nursing Fac.   12,919 providers · 39,391,060 benes · 0.328 / 1k · $27.85B
--  (the per-1k national ranking is led by physician categories — Preventive
--   Health 5.703, Pulmonology 4.556 — which carry hundreds of thousands of
--   billing clinicians; the program-integrity story is the COUNTY clustering
--   of the supplier categories below, not the national physician ratio.)

-- ============================================================================
-- (2) Home health county reconciliation — county presences vs distinct firms.
-- ============================================================================
WITH latest AS (SELECT max(reference_period) AS p FROM public.cms_market_saturation)
SELECT
  (SELECT number_of_providers::bigint FROM public.cms_market_saturation m, latest
     WHERE aggregation_level = 'NATION + TERRITORIES' AND reference_period = latest.p
       AND type_of_service = 'Home Health')                            AS national_distinct_agencies,
  (SELECT sum(number_of_providers)::bigint FROM public.cms_market_saturation m, latest
     WHERE aggregation_level = 'COUNTY' AND reference_period = latest.p
       AND type_of_service = 'Home Health')                            AS sum_county_presences
FROM latest;
--  national_distinct_agencies 8,488 · sum_county_presences 23,311
--  (avg agency present in ~2.75 counties; county counts are presences.)

-- ============================================================================
-- (3) HEADLINE: home health counties by agency count (latest period).
--     Los Angeles is the most home-health-saturated large county in the
--     country: 1,847 agencies, 2.12 per 1,000 FFS benes (9.9x national 0.215).
-- ============================================================================
WITH latest AS (SELECT max(reference_period) AS p FROM public.cms_market_saturation)
SELECT
  county, state, geo_fips,
  number_of_providers::bigint                                          AS agencies,
  number_of_ffs_beneficiaries::bigint                                  AS ffs_benes,
  number_of_users::bigint                                              AS users,
  average_users_per_provider                                           AS users_per_agency,
  round(1000.0 * number_of_providers
        / nullif(number_of_ffs_beneficiaries, 0), 2)                   AS agencies_per_1k
FROM public.cms_market_saturation m, latest
WHERE aggregation_level = 'COUNTY' AND reference_period = latest.p
  AND type_of_service = 'Home Health'
ORDER BY agencies DESC NULLS LAST
LIMIT 8;
--  LOS ANGELES   CA  1,847  872,904  72.39 u/agency  2.12 / 1k   <- lead
--  COOK          IL    365  564,951                  0.65 / 1k
--  ORANGE        CA    312  308,173                  1.01 / 1k
--  DALLAS        TX    232  202,655                  1.14 / 1k
--  SAN BERNARDINO CA   214  146,380                  1.46 / 1k
--  HARRIS        TX    201  316,157                  0.64 / 1k
--  RIVERSIDE     CA    178  200,452                  0.89 / 1k
--  MIAMI-DADE    FL    172  174,283                  0.99 / 1k

-- ============================================================================
-- (4) Home health saturation RATE — top counties with a stable base
--     (>= 20,000 FFS beneficiaries so the ratio is not a small-county artifact).
-- ============================================================================
WITH latest AS (SELECT max(reference_period) AS p FROM public.cms_market_saturation)
SELECT
  county, state,
  number_of_providers::bigint                                          AS agencies,
  number_of_ffs_beneficiaries::bigint                                  AS ffs_benes,
  round(1000.0 * number_of_providers
        / nullif(number_of_ffs_beneficiaries, 0), 2)                   AS agencies_per_1k
FROM public.cms_market_saturation m, latest
WHERE aggregation_level = 'COUNTY' AND reference_period = latest.p
  AND type_of_service = 'Home Health'
  AND number_of_ffs_beneficiaries >= 20000
ORDER BY agencies_per_1k DESC NULLS LAST
LIMIT 9;
--  LOS ANGELES CA 2.12 · IMPERIAL CA 1.67 · SAN BERNARDINO CA 1.46
--  GUADALUPE TX 1.40 · CAMERON TX 1.37 · HIDALGO (McAllen) TX 1.31
--  GRAYSON TX 1.28 · VENTURA CA 1.15 · DALLAS TX 1.14
--  (the Texas border counties — Hidalgo, Cameron, Guadalupe — are the
--   classic home health program-integrity corridor alongside Southern California.)

-- ============================================================================
-- (5) State-level home health saturation. California leads at 0.604 per 1,000
--     FFS benes — nearly triple the national 0.215 and the highest of any state.
-- ============================================================================
WITH latest AS (SELECT max(reference_period) AS p FROM public.cms_market_saturation)
SELECT
  state,
  number_of_providers::bigint                                          AS agencies,
  number_of_ffs_beneficiaries::bigint                                  AS ffs_benes,
  round(1000.0 * number_of_providers
        / nullif(number_of_ffs_beneficiaries, 0), 3)                   AS agencies_per_1k
FROM public.cms_market_saturation m, latest
WHERE aggregation_level = 'STATE' AND reference_period = latest.p
  AND type_of_service = 'Home Health'
ORDER BY agencies_per_1k DESC NULLS LAST
LIMIT 8;
--  CA 0.604 · NV 0.517 · DC 0.381 · TX 0.371 · OK 0.336 · LA 0.320 · IL 0.293 · FL 0.282
--  (national 0.215; CA / NV / TX lead.)

-- ============================================================================
-- (6) Durable medical equipment — most-saturated large counties
--     (>= 50,000 FFS benes). National DME rate 1.161 / 1k; these run ~3x it.
-- ============================================================================
WITH latest AS (SELECT max(reference_period) AS p FROM public.cms_market_saturation)
SELECT
  county, state,
  number_of_providers::bigint                                          AS suppliers,
  number_of_ffs_beneficiaries::bigint                                  AS ffs_benes,
  round(1000.0 * number_of_providers
        / nullif(number_of_ffs_beneficiaries, 0), 2)                   AS suppliers_per_1k
FROM public.cms_market_saturation m, latest
WHERE aggregation_level = 'COUNTY' AND reference_period = latest.p
  AND type_of_service = 'Durable Medical Equipment'
  AND number_of_ffs_beneficiaries >= 50000
ORDER BY suppliers_per_1k DESC NULLS LAST
LIMIT 10;
--  FORT BEND TX 3.45 · WILL IL 3.43 · MONTGOMERY TX 3.43 · PASCO FL 3.41
--  BALTIMORE CITY MD 3.35 · SUMMIT OH 3.35 · KANE IL 3.35 · HUDSON NJ 3.34
--  CAMDEN NJ 3.33 · DENTON TX 3.32

-- ============================================================================
-- (7) The enrollment-MORATORIUM flag — an honest empty state.
--     CMS retains a moratorium flag on every record. In this snapshot it reads
--     "No" on every row: there is NO active enrollment moratorium anywhere.
--     The 2013-2019 statewide home health / ground ambulance moratoria were all
--     lifted in January 2019, so saturation here is a monitoring signal only.
-- ============================================================================
WITH latest AS (SELECT max(reference_period) AS p FROM public.cms_market_saturation)
SELECT coalesce(moratorium, '<null>') AS moratorium_flag, count(*) AS rows
FROM public.cms_market_saturation m, latest
WHERE reference_period = latest.p
GROUP BY 1
ORDER BY 2 DESC;
--  No        68,044
--  <null>        24
--  (zero rows read "Yes" — in this period or in ANY of the 15 periods;
--   distinct moratorium values across the whole table = {No, NULL}.)

-- ============================================================================
-- (8) Universe reconciliation — period / aggregation-level split.
-- ============================================================================
WITH latest AS (SELECT max(reference_period) AS p FROM public.cms_market_saturation)
SELECT
  (SELECT count(*) FROM public.cms_market_saturation)                  AS all_rows,
  (SELECT count(DISTINCT reference_period) FROM public.cms_market_saturation) AS periods,
  (SELECT count(DISTINCT type_of_service) FROM public.cms_market_saturation)  AS service_types,
  (SELECT count(*) FROM public.cms_market_saturation m, latest
     WHERE reference_period = latest.p)                                AS rows_latest,
  (SELECT count(DISTINCT geo_fips) FROM public.cms_market_saturation m, latest
     WHERE aggregation_level = 'COUNTY' AND reference_period = latest.p) AS counties_latest
FROM latest;
--  all_rows 1,030,290 · periods 15 · service_types 24
--  rows_latest 68,068 · counties_latest 3,141
