-- Where nursing-home penalties concentrate: a repeat-citation story, 2026
-- Fonteum reproducible query — https://fonteum.com/research/nursing-home-penalty-concentration-2026
-- Data as of: 2026-06-16  ·  methodology: cms-cmp/v1
-- Source datasets: CMS Nursing Home Compare
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- WHERE Medicare's nursing-home civil money penalties concentrate — and why it
-- is a repeat-citation story. Fully reproducible query.
--
-- Question: of the civil money penalties (CMPs) and payment denials CMS has
-- imposed on Medicare/Medicaid-certified nursing facilities, how are the dollars
-- and the severe actions distributed across facilities, fine sizes, and states?
-- The lead figure: of the 6,884 facilities penalized between May 2023 and April
-- 2026, the 3,699 cited more than once (53.7%) carry 80.1% of all fine dollars
-- and 90.6% of the payment denials. A penalty is a CMS enforcement action, NOT
-- a Fonteum judgment, fraud signal, or assessment of current facility quality.
--
-- Source:
--   public.cms_civil_money_penalties — CMS "Civil Money Penalties" public-use
--     file, published quarterly via the CMS data catalog (data.cms.gov, dataset
--     g6vv-u9sr). 16,277 enforcement actions; source release 2026-05-01.
--     Public, read-only. License: US-Government-Works (17 U.S.C. Sec. 105).
--     methodology_version = 'cms-cmp/v1'.
--
-- Universe: this study reads the published file AS A WHOLE — every row is one
--   enforcement action CMS records against a certified facility, keyed to the
--   facility CMS Certification Number (CCN). The file carries NO provider NPI;
--   penalties join to facilities, never to a named clinician. It is the current
--   enforcement record CMS releases each quarter, not a cumulative history.
--
-- Counting note: two penalty types appear. A "Fine" carries a fine_amount; a
--   "Payment Denial" suspends Medicare/Medicaid payment for new admissions and
--   carries a payment_denial_days length but NO dollar amount. All dollar totals
--   below sum fine actions only. No individual facility is named in the study.

-- ============================================================================
-- (1) Universe reconciliation — the published file at a glance.
-- ============================================================================
SELECT
  count(*)                                                          AS actions,
  count(DISTINCT ccn)                                               AS distinct_ccn,
  count(DISTINCT state)                                             AS states,
  count(*) FILTER (WHERE penalty_type = 'Fine')                     AS fine_actions,
  round(sum(fine_amount) FILTER (WHERE penalty_type = 'Fine'))      AS total_fine_usd,
  count(*) FILTER (WHERE penalty_type = 'Payment Denial')           AS payment_denials,
  min(penalty_date)                                                 AS earliest_action,
  max(penalty_date)                                                 AS latest_action,
  max(source_release_date)                                          AS source_release
FROM public.cms_civil_money_penalties;
--  actions 16,277 · distinct_ccn 6,884 · states 53 · fine_actions 13,764
--  total_fine_usd 459,337,807 · payment_denials 2,513
--  earliest 2023-05-17 · latest 2026-04-17 · source_release 2026-05-01

-- ============================================================================
-- (2) HEADLINE: enforcement is concentrated on repeat-cited facilities. Roll up
--     to the facility (CCN), bucket by how many actions each facility drew, and
--     read the share of dollars and payment denials per bucket. The 2-or-more
--     buckets together = 53.7% of facilities, 80.1% of dollars, 90.6% of denials.
-- ============================================================================
WITH fac AS (
  SELECT
    ccn,
    count(*)                                                        AS actions,
    sum(coalesce(fine_amount, 0))                                   AS fines,
    count(*) FILTER (WHERE penalty_type = 'Payment Denial')         AS denials
  FROM public.cms_civil_money_penalties
  GROUP BY ccn
), bucketed AS (
  SELECT
    CASE WHEN actions = 1 THEN '1'
         WHEN actions BETWEEN 2 AND 4 THEN '2-4'
         WHEN actions BETWEEN 5 AND 9 THEN '5-9'
         ELSE '10+' END                                             AS penalties_per_facility,
    actions, fines, denials
  FROM fac
)
SELECT
  penalties_per_facility,
  count(*)                                                          AS facilities,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1)                AS pct_facilities,
  round(sum(fines))                                                 AS fine_dollars,
  round(100.0 * sum(fines) / sum(sum(fines)) OVER (), 1)            AS pct_dollars,
  sum(denials)                                                      AS payment_denials,
  round(100.0 * sum(denials) / sum(sum(denials)) OVER (), 1)        AS pct_denials
FROM bucketed
GROUP BY penalties_per_facility
ORDER BY min(actions);
--  1    3,185  46.3%  $ 91,417,534  19.9%  236   9.4%
--  2-4  2,983  43.3%  $237,324,673  51.7% 1,397  55.6%
--  5-9    597   8.7%  $108,506,786  23.6%  737  29.3%
--  10+    119   1.7%  $ 22,088,814   4.8%  143   5.7%
--  cited >1x  = 3,699 facilities (53.7%), 80.1% of dollars, 2,277 denials (90.6%)
--  cited >=5x =   716 facilities (10.4%), 28.4% of dollars,   880 denials (35.0%)

-- ============================================================================
-- (3) The fine-amount distribution is itself skewed: a thick base of small fines
--     and a thin tail of very large ones. The median fine is $14,576, but the
--     1,138 fines of $100k+ (8.3% of fine actions) carry 40.7% of every dollar.
-- ============================================================================
SELECT
  round(percentile_cont(0.5)  WITHIN GROUP (ORDER BY fine_amount)
        FILTER (WHERE fine_amount > 0))                             AS median_fine,
  round(percentile_cont(0.9)  WITHIN GROUP (ORDER BY fine_amount)
        FILTER (WHERE fine_amount > 0))                             AS p90_fine,
  round(percentile_cont(0.99) WITHIN GROUP (ORDER BY fine_amount)
        FILTER (WHERE fine_amount > 0))                             AS p99_fine,
  max(fine_amount)                                                  AS max_fine,
  count(*) FILTER (WHERE fine_amount >= 100000)                     AS fines_100k_plus,
  round(100.0 * sum(fine_amount) FILTER (WHERE fine_amount >= 100000)
        / sum(fine_amount) FILTER (WHERE fine_amount > 0), 1)       AS pct_dollars_100k_plus,
  count(*) FILTER (WHERE fine_amount > 0 AND fine_amount < 10000)   AS fines_under_10k
FROM public.cms_civil_money_penalties;
--  median_fine $14,576 · p90 $86,392 · p99 $229,243 · max $713,795
--  fines_100k_plus 1,138 · pct_dollars_100k_plus 40.7% · fines_under_10k 4,753

-- ============================================================================
-- (4) WHERE the dollars are — top 8 states by total fine dollars, with each
--     state's facility count, payment denials, and average fine. Illinois leads
--     ($66.1M, 14.4% of all dollars) and records 496 payment denials — about one
--     in five nationwide — at the highest average fine of the largest states.
-- ============================================================================
SELECT
  state,
  count(DISTINCT ccn)                                               AS facilities,
  round(sum(coalesce(fine_amount, 0)))                              AS fine_dollars,
  round(100.0 * sum(coalesce(fine_amount, 0))
        / sum(sum(coalesce(fine_amount, 0))) OVER (), 1)            AS pct_dollars,
  count(*) FILTER (WHERE penalty_type = 'Payment Denial')           AS payment_denials,
  round(avg(fine_amount) FILTER (WHERE fine_amount > 0))            AS avg_fine
FROM public.cms_civil_money_penalties
WHERE state IS NOT NULL
GROUP BY state
ORDER BY fine_dollars DESC
LIMIT 8;
--  IL 467 $66,149,870 14.4% 496 $50,190 · TX 826 $59,272,333 12.9% 162 $32,074
--  CA 526 $30,757,146  6.7% 287 $25,760 · OH 318 $21,223,444  4.6% 169 $40,580
--  FL 284 $19,252,995  4.2%  23 $28,650 · MO 233 $17,073,306  3.7% 151 $34,011
--  PA 298 $17,000,120  3.7%  58 $31,717 · NC 249 $16,499,013  3.6%  82 $31,790

-- ============================================================================
-- (5) Payment denials are the severe tier — denial of payment for new admissions
--     until deficiencies are corrected. They cluster with repeat-cited
--     facilities (90.6% fall on facilities cited >1x, see query 2) and run 27
--     days on average. CMS attaches no dollar amount to a denial action.
-- ============================================================================
SELECT
  count(*) FILTER (WHERE payment_denial_days > 0)                   AS payment_denials,
  round(avg(payment_denial_days) FILTER (WHERE payment_denial_days > 0)) AS avg_days,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY payment_denial_days)
        FILTER (WHERE payment_denial_days > 0)                      AS median_days,
  max(payment_denial_days)                                          AS max_days
FROM public.cms_civil_money_penalties;
--  payment_denials 2,513 · avg_days 27 · median_days 19 · max_days 458

-- ============================================================================
-- (6) The action window by year, for context only (NOT a trend model). The file
--     spans a partial 2023 (from May 17) through a partial 2026 (to April 17),
--     so the first and last years are incomplete. Counts are by penalty_date.
-- ============================================================================
SELECT
  extract(year FROM penalty_date)::int                              AS action_year,
  count(*)                                                          AS actions,
  count(*) FILTER (WHERE penalty_type = 'Fine')                     AS fine_actions,
  round(sum(coalesce(fine_amount, 0)))                              AS fine_dollars,
  count(*) FILTER (WHERE penalty_type = 'Payment Denial')           AS payment_denials
FROM public.cms_civil_money_penalties
GROUP BY action_year
ORDER BY action_year;
--  2023 5,539 4,876 $117,210,368 663 (partial, from 2023-05-17)
--  2024 6,170 5,098 $183,989,128 1,072
--  2025 4,162 3,423 $143,381,741 739
--  2026   406   367 $ 14,756,570  39 (partial, to 2026-04-17)
