What Medicare actually uses: service penetration and caseload, 2025

-- What Medicare actually USES — the demand-side (Utilization) half of the CMS
-- Market Saturation & Utilization file. Fully reproducible query.
--
-- Question: of the 24 Medicare fee-for-service service categories CMS tracks,
-- how much of the beneficiary population does each one actually reach
-- (penetration), how many beneficiaries does each provider carry (caseload),
-- and which services concentrate among low-income dual-eligible beneficiaries?
-- The lead figure: preventive health services reaches 53.6% of FFS
-- beneficiaries — the ONLY category above half — while the median category
-- reaches under 6%. Utilization is a REACH measure: it says nothing about the
-- quality of care, the volume billed, or any provider's conduct.
--
-- Source:
--   public.cms_market_saturation — CMS "Market Saturation & Utilization
--     State-County" public-use files, 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, three aggregation levels (NATION + TERRITORIES, STATE,
--     COUNTY). Snapshot release 2025-12-01. Public, read-only.
--     License: US-Government-Works (17 U.S.C. Sec. 105).
--     methodology_version = 'market-saturation/v1'.
--
-- Period: this study reads only the MOST RECENT annual period, resolved as
--   max(reference_period) rather than hard-coded, so figures advance when CMS
--   publishes the next file. Latest = '2025-01-01 to 2025-12-31', 68,068 rows
--   (24 NATION + TERRITORIES rollups, 1,219 STATE, 66,825 COUNTY).
--
-- Counting note: "users" counts DISTINCT beneficiaries who used a service in
--   the period — not visits, claims, or dollars. Penetration = users / FFS
--   beneficiaries. The data is aggregate by construction: no individual
--   beneficiary or provider appears, and none is named in the study.

-- ============================================================================
-- (0) Universe reconciliation — the table and the latest period at a glance.
-- ============================================================================
SELECT
  count(*)                                                          AS total_rows,
  count(DISTINCT reference_period)                                  AS periods,
  count(DISTINCT type_of_service)                                   AS services,
  max(reference_period)                                             AS latest_period,
  max(source_release_date)                                          AS release
FROM public.cms_market_saturation;
--  total_rows 1,030,290 · periods 15 · services 24
--  latest_period '2025-01-01 to 2025-12-31' · release 2025-12-01

SELECT
  count(*)                                                          AS latest_period_rows,
  count(*) FILTER (WHERE aggregation_level ILIKE 'NATION%')         AS nation_rows,
  count(*) FILTER (WHERE aggregation_level ILIKE 'STATE%')          AS state_rows,
  count(*) FILTER (WHERE aggregation_level ILIKE 'COUNTY%')         AS county_rows
FROM public.cms_market_saturation
WHERE reference_period = '2025-01-01 to 2025-12-31';
--  latest_period_rows 68,068 · nation 24 · state 1,219 · county 66,825

-- ============================================================================
-- (1) HEADLINE: penetration by service (national). Share of FFS beneficiaries
--     who actually USE each service. Only preventive health clears 50%; the
--     median category reaches under 6%; 17 of 24 reach under 10%.
-- ============================================================================
SELECT
  type_of_service,
  number_of_providers                                              AS providers,
  number_of_users                                                  AS beneficiaries_reached,
  number_of_ffs_beneficiaries                                      AS ffs,
  round(100.0 * number_of_users
        / nullif(number_of_ffs_beneficiaries, 0), 1)               AS pct_users_of_ffs
FROM public.cms_market_saturation
WHERE reference_period = '2025-01-01 to 2025-12-31'
  AND aggregation_level ILIKE 'NATION%'
ORDER BY number_of_users DESC;
--  Preventive Health Services            225,876  21,248,672  39,609,673  53.6%   <- only category > 50%
--  Indep. Diagnostic Testing Facility A    3,883  16,742,549  39,608,339  42.3%
--  Clinical Laboratory (Billing Indep.)    2,303  15,554,744  39,608,749  39.3%
--  Ophthalmology                          47,498  12,283,238  39,604,303  31.0%
--  Pulmonology                           180,428   9,326,391  39,602,413  23.6%
--  Durable Medical Equipment              45,963   7,358,733  39,598,270  18.6%
--  Podiatry Services                      15,172   4,425,256  39,532,230  11.2%
--  Physical & Occupational Therapy        95,124   3,839,619  39,522,162   9.7%
--  Home Health                             8,488   2,613,941  39,524,879   6.6%
--  Psychotherapy                          35,557   2,108,821  39,480,134   5.3%
--  Skilled Nursing Facility               12,919   1,141,379  39,391,060   2.9%
--  Hospice                                 5,024     915,164  39,475,379   2.3%
--  Dialysis                                6,409     235,402  38,046,460   0.6%
--  (… 24 categories total; tail includes Endocrinology 0.7%, Neurology 1.2%,
--     Cardiac Rehab 0.5%, Long-Term Care Hospitals 0.1%.)

-- Distribution summary — confirms "only one over 50%, median under 6%".
SELECT
  count(*)                                                          AS services,
  count(*) FILTER (WHERE percentage_users_of_ffs > 50)              AS over_50pct,
  count(*) FILTER (WHERE percentage_users_of_ffs >= 10)             AS at_least_10pct,
  count(*) FILTER (WHERE percentage_users_of_ffs < 10)              AS under_10pct,
  round(percentile_cont(0.5) WITHIN GROUP
        (ORDER BY percentage_users_of_ffs)::numeric, 2)             AS median_pct
FROM public.cms_market_saturation
WHERE reference_period = '2025-01-01 to 2025-12-31'
  AND aggregation_level ILIKE 'NATION%';
--  services 24 · over_50pct 1 · at_least_10pct 7 · under_10pct 17 · median_pct 5.98

-- ============================================================================
-- (2) CASELOAD: average users per provider (national). A handful of facility-
--     based services carry thousands of beneficiaries each; office-based
--     services carry dozens. The spread runs 6,754 (labs) to 37 (dialysis),
--     a factor of ~184.
-- ============================================================================
SELECT
  type_of_service,
  number_of_providers                                              AS providers,
  number_of_users                                                  AS beneficiaries_reached,
  average_users_per_provider                                       AS users_per_provider
FROM public.cms_market_saturation
WHERE reference_period = '2025-01-01 to 2025-12-31'
  AND aggregation_level ILIKE 'NATION%'
ORDER BY average_users_per_provider DESC;
--  Clinical Laboratory                  2,303  15,554,744  6,754
--  Indep. Diagnostic Testing Facility A 3,883  16,742,549  4,312
--  Ambulance (Emergency & Non-Emergency)8,504   3,752,418    441
--  Home Health                          8,488   2,613,941    308
--  Ophthalmology                       47,498  12,283,238    259
--  Hospice                              5,024     915,164    182
--  Durable Medical Equipment           45,963   7,358,733    160
--  Psychotherapy                       35,557   2,108,821     59
--  Pulmonology                        180,428   9,326,391     52
--  Chiropractic Services               25,597   1,329,758     52
--  Physical & Occupational Therapy     95,124   3,839,619     40
--  Dialysis                             6,409     235,402     37   <- lowest
--  max/min = 6,754 / 37 = 184x

-- ============================================================================
-- (3) DUAL-ELIGIBLE MIX: share of each service's users who are dual-eligible
--     (Medicare + Medicaid), a marker of the low-income population. FQHCs top
--     the list at 40.7%; chiropractic is lowest at 4.4%. Services with > 200k
--     users shown so small-base ratios do not distort the ranking.
-- ============================================================================
SELECT
  type_of_service,
  number_of_users                                                  AS beneficiaries_reached,
  number_of_dual_eligible_users                                    AS dual_users,
  percentage_dual_of_total_users                                   AS dual_share_pct
FROM public.cms_market_saturation
WHERE reference_period = '2025-01-01 to 2025-12-31'
  AND aggregation_level ILIKE 'NATION%'
  AND number_of_users > 200000
ORDER BY percentage_dual_of_total_users DESC NULLS LAST;
--  Federally Qualified Health Center  1,492,047    606,466  40.65%  <- highest
--  Dialysis                             235,402     80,518  34.20%
--  Psychotherapy                      2,108,821    547,860  25.98%
--  Telemedicine                       1,375,348    347,465  25.26%
--  Ambulance (Non-Emergency)          1,323,893    333,214  25.17%
--  Skilled Nursing Facility           1,141,379    275,248  24.12%
--  Home Health                        2,613,941    484,617  18.54%
--  Durable Medical Equipment          7,358,733  1,015,788  13.80%
--  Preventive Health Services        21,248,672  2,300,831  10.83%
--  Ophthalmology                     12,283,238  1,094,170   8.91%
--  Physical & Occupational Therapy    3,839,619    303,478   7.90%
--  Chiropractic Services              1,329,758     58,405   4.39%  <- lowest

-- ============================================================================
-- (4) GEOGRAPHIC VARIATION: preventive-care penetration by state (the single
--     most-used service). Even it ranges from 67.6% (DE) to 43.3% (ME) —
--     a 24.3-point gap around the national 53.6%. States with >= 100k FFS
--     beneficiaries only, so small-state ratios do not distort the ranking.
-- ============================================================================
WITH s AS (
  SELECT
    state,
    percentage_users_of_ffs                                        AS pen,
    number_of_users                                                AS users,
    number_of_ffs_beneficiaries                                    AS ffs
  FROM public.cms_market_saturation
  WHERE reference_period = '2025-01-01 to 2025-12-31'
    AND aggregation_level ILIKE 'STATE%'
    AND type_of_service = 'Preventive Health Services'
    AND number_of_ffs_beneficiaries > 100000
)
(SELECT 'HIGH' AS tag, state, pen, users, ffs FROM s ORDER BY pen DESC LIMIT 5)
UNION ALL
(SELECT 'LOW'  AS tag, state, pen, users, ffs FROM s ORDER BY pen ASC  LIMIT 5)
ORDER BY tag, pen DESC;
--  HIGH  DE 67.58  ·  SC 61.95  ·  MA 60.42  ·  MD 59.37  ·  NJ 59.32
--  LOW   HI 45.36  ·  ID 44.78  ·  AK 44.64  ·  OR 44.07  ·  ME 43.34
--  national preventive-health penetration = 53.6%; gap of the displayed rounded
--  state rates 67.6 - 43.3 = 24.3 points (raw 67.58 - 43.34 = 24.24)