-- Medicare's largest value-based program is a long tail with a heavy head
-- Fonteum reproducible query — https://fonteum.com/research/mssp-aco-participation-concentration-2026
-- Data as of: 2026-06-16  ·  methodology: cms-mssp-aco/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).

-- How concentrated is Medicare's largest value-based-care program? The Medicare
-- Shared Savings Program (MSSP) looks like a broad network — 511 accountable
-- care organizations (ACOs) for performance year 2026 — but its weight sits in
-- a small head. Fully reproducible query.
--
-- Question: across the published MSSP ACO Participants file, how are the 15,329
-- participant organizations distributed across the 511 ACOs, which risk track
-- and revenue tier do the large ACOs sit in, and how concentrated is the
-- network? The lead figure: the largest 10% of ACOs (51 of 511) hold 43.5% of
-- every participant organization on the file. MSSP participation is a
-- value-based-care program ENROLLMENT record — it is NOT a measure of an ACO's
-- shared-savings performance, cost, quality, or outcomes of any kind.
--
-- Source:
--   public.cms_mssp_aco_participants — CMS "Accountable Care Organizations"
--     (ACO Participants) public-use file, published annually via the CMS data
--     catalog (data.cms.gov, Medicare Shared Savings Program). 15,329
--     participant rows; 511 distinct ACOs; performance year 2026; CMS release
--     2026-01-22. Public, read-only. License: US-Government-Works (17 U.S.C.
--     Sec. 105). methodology_version = 'cms-mssp-aco/v1'.
--
-- Universe: this study reads the published file AS A WHOLE — every row is one
--   participant legal business name (LBN) that CMS lists as enrolled in an MSSP
--   ACO for PY2026. The public file carries NO participant TIN or NPI (those are
--   restricted to ResDAC), so there is no individual-provider identifier here;
--   the unit is the participant organization (LBN), not a person. No individual
--   provider is named, ranked, or scored. Counts are exact over the file.
--
-- Counting note: "participant organization" = one row (one LBN under one ACO).
--   "ACO size" = the count of participant LBNs in an ACO. 15,293 distinct LBNs
--   across 15,329 rows (a handful of LBNs appear under more than one ACO).

-- ============================================================================
-- (1) Universe reconciliation — the published file at a glance.
-- ============================================================================
SELECT
  count(*)                                                          AS participant_rows,
  count(DISTINCT aco_id)                                            AS acos,
  count(DISTINCT participant_lbn)                                   AS distinct_lbn,
  count(DISTINCT performance_year)                                  AS performance_years,
  max(performance_year)                                             AS performance_year,
  max(source_release_date)                                          AS cms_release,
  max(methodology_version)                                          AS methodology_version
FROM public.cms_mssp_aco_participants;
--  participant_rows 15,329 · acos 511 · distinct_lbn 15,293
--  performance_years 1 · performance_year 2026 · cms_release 2026-01-22
--  methodology_version cms-mssp-aco/v1

-- ============================================================================
-- (2) HEADLINE: ACO size distribution. Each ACO's size = its count of
--     participant organizations. The network is a long tail with a heavy head:
--     a quarter of ACOs (125) hold 5 participants or fewer — just 2.0% of the
--     network — while 17 ACOs of 121+ participants hold 23.2%.
-- ============================================================================
WITH sz AS (
  SELECT aco_id, count(*) AS p
  FROM public.cms_mssp_aco_participants
  GROUP BY aco_id
)
SELECT
  CASE
    WHEN p BETWEEN 1 AND 5    THEN '1-5'
    WHEN p BETWEEN 6 AND 15   THEN '6-15'
    WHEN p BETWEEN 16 AND 30  THEN '16-30'
    WHEN p BETWEEN 31 AND 60  THEN '31-60'
    WHEN p BETWEEN 61 AND 120 THEN '61-120'
    ELSE '121+'
  END                                                               AS size_band,
  count(*)                                                          AS acos,
  sum(p)                                                            AS participants,
  round(100.0 * sum(p) / (SELECT sum(p) FROM sz), 1)                AS pct_of_network
FROM sz
GROUP BY size_band
ORDER BY min(p);
--  1-5      125 acos ·   314 participants ·  2.0%
--  6-15     109 acos · 1,137 participants ·  7.4%
--  16-30    124 acos · 2,651 participants · 17.3%
--  31-60     87 acos · 3,560 participants · 23.2%
--  61-120    49 acos · 4,113 participants · 26.8%
--  121+      17 acos · 3,554 participants · 23.2%

-- ============================================================================
-- (3) Concentration, computed directly. Rank ACOs by size and read the
--     cumulative share off the top. The largest 10% of ACOs (51) hold 43.5% of
--     all participant organizations; the smallest half (256) hold 11.8%.
--     Median ACO = 17 participants, mean = 30.0, largest = 822, smallest = 1.
-- ============================================================================
WITH sz AS (
  SELECT aco_id, count(*) AS p FROM public.cms_mssp_aco_participants GROUP BY aco_id
), ranked AS (
  SELECT p, row_number() OVER (ORDER BY p DESC) AS rk, sum(p) OVER () AS tot
  FROM sz
)
SELECT
  (SELECT round(avg(p), 1) FROM sz)                                 AS mean_size,
  (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY p) FROM sz)   AS median_size,
  (SELECT max(p) FROM sz)                                           AS largest_aco,
  (SELECT min(p) FROM sz)                                           AS smallest_aco,
  (SELECT count(*) FROM sz WHERE p = 1)                             AS single_participant_acos,
  (SELECT round(100.0 * sum(p) / max(tot), 1) FROM ranked WHERE rk <= 51)  AS top10pct_share,
  (SELECT round(100.0 * sum(p) / max(tot), 1) FROM ranked WHERE rk <= 26)  AS top5pct_share,
  (SELECT round(100.0 * sum(p) / max(tot), 1) FROM ranked WHERE rk <= 10)  AS top10_aco_share,
  (SELECT round(100.0 * sum(p) / max(tot), 1) FROM ranked WHERE rk > 255)  AS bottom50pct_share
FROM ranked
LIMIT 1;
--  mean_size 30.0 · median_size 17 · largest_aco 822 · smallest_aco 1
--  single_participant_acos 44
--  top10pct_share 43.5% (51 ACOs) · top5pct_share 29.7% (26 ACOs)
--  top10_aco_share 17.3% (10 ACOs) · bottom50pct_share 11.8% (256 ACOs)

-- ============================================================================
-- (4) The full-risk track carries the network's weight. CMS runs MSSP on two
--     risk models: BASIC (a glide path, lower downside risk) and ENHANCED (the
--     full two-sided-risk model). Enhanced ACOs are 57.9% of ACOs but hold
--     71.0% of all participant organizations, because they are larger on
--     average (36.8 participants vs 20.7 for Basic). Risk and scale move
--     together. These are mutually exclusive per ACO (296 + 215 = 511).
-- ============================================================================
WITH aco AS (
  SELECT aco_id, bool_or(enhanced_track) AS enhanced, count(*) AS p
  FROM public.cms_mssp_aco_participants
  GROUP BY aco_id
)
SELECT
  CASE WHEN enhanced THEN 'Enhanced (full risk)' ELSE 'Basic (glide path)' END AS track,
  count(*)                                                          AS acos,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1)                AS pct_of_acos,
  sum(p)                                                            AS participants,
  round(100.0 * sum(p) / sum(sum(p)) OVER (), 1)                    AS pct_of_network,
  round(avg(p), 1)                                                  AS mean_size
FROM aco
GROUP BY enhanced
ORDER BY participants DESC;
--  Enhanced (full risk)  296 acos · 57.9% · 10,885 participants · 71.0% · mean 36.8
--  Basic (glide path)    215 acos · 42.1% ·  4,444 participants · 29.0% · mean 20.7

-- ============================================================================
-- (5) Revenue tier is NOT size. CMS classifies each ACO as HIGH- or
--     LOW-revenue by the ratio of its participants' Medicare revenue to its
--     assigned-beneficiary spending — a structural label about practice type
--     (hospital-anchored vs physician-led), not about how many organizations an
--     ACO contains. The two tiers' mean participant counts are nearly identical,
--     so the concentration in (2)-(4) is not a revenue-tier artifact.
-- ============================================================================
WITH aco AS (
  SELECT aco_id,
         bool_or(high_revenue_aco) AS high_rev,
         bool_or(low_revenue_aco)  AS low_rev,
         count(*) AS p
  FROM public.cms_mssp_aco_participants
  GROUP BY aco_id
)
SELECT
  count(*) FILTER (WHERE high_rev)                                  AS high_revenue_acos,
  round(avg(p) FILTER (WHERE high_rev), 1)                          AS high_rev_mean_size,
  count(*) FILTER (WHERE low_rev)                                   AS low_revenue_acos,
  round(avg(p) FILTER (WHERE low_rev), 1)                           AS low_rev_mean_size
FROM aco;
--  high_revenue_acos 186 · high_rev_mean_size 31.0
--  low_revenue_acos  325 · low_rev_mean_size  29.4

-- ============================================================================
-- (6) Service area is a CMS label, not a clean state key. Most ACOs operate
--     across more than one state, so aco_service_area is frequently a
--     multi-state string. Of 281 distinct service-area labels, 242 span more
--     than one state, and 311 of the 511 ACOs carry a multi-state label. The
--     single-state labels that recur most are CA, FL, and TX — the same large
--     markets that anchor most Medicare provider geographies.
-- ============================================================================
SELECT
  (SELECT count(DISTINCT aco_service_area) FROM public.cms_mssp_aco_participants)
                                                                    AS distinct_service_areas,
  (SELECT count(DISTINCT aco_service_area) FROM public.cms_mssp_aco_participants
     WHERE length(aco_service_area) > 2)                            AS multistate_labels,
  (SELECT count(DISTINCT aco_id) FROM public.cms_mssp_aco_participants
     WHERE length(aco_service_area) > 2)                            AS acos_multistate;
--  distinct_service_areas 281 · multistate_labels 242 · acos_multistate 311
--  single-state labels recurring most: CA 25 ACOs · FL 18 · TX 14 · PA 10 · NY 10
