-- The rising size of nursing-home fines: a three-year trend, 2026
-- Fonteum reproducible query — https://fonteum.com/research/nursing-home-fine-size-trend-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).

-- HOW the size of CMS nursing-home civil money penalties has moved over three
-- years. Fully reproducible query.
--
-- Question: across the civil money penalties (CMPs) CMS has imposed on
-- Medicare/Medicaid-certified nursing facilities, has the SIZE of the typical
-- fine changed over time? The lead figure: the median fine more than doubled,
-- from $8,193 in 2023 to $22,315 in the partial 2026, and rose 21% between the
-- two complete calendar years ($16,055 in 2024 to $19,412 in 2025). The climb is
-- broad-based — the bottom-quartile fine more than tripled and the share of
-- fines under $10,000 collapsed — not a few large outliers pulling the average.
-- A penalty is a CMS enforcement action, NOT a Fonteum judgment, fraud signal,
-- or assessment of current facility quality. The file records the AMOUNTS CMS
-- set; it does not record why they moved, and this query infers no cause.
--
-- 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. The window runs
--   from a partial 2023 (actions dated from 2023-05-17) through a partial 2026
--   (to 2026-04-17), so the first and last calendar years are incomplete.
--
-- Counting note: two penalty types appear. A "Fine" carries a fine_amount; a
--   "Payment Denial" suspends Medicare/Medicaid payment for new admissions and
--   carries no dollar amount. Every size figure below is over fine actions only.
--   Per-action statistics (median, percentiles) are unaffected by the partial
--   first/last years; annual COUNTS are not, because recent actions report into
--   the file with a lag (see query 6). No individual facility is named.

-- ============================================================================
-- (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,
  round(percentile_cont(0.5) WITHIN GROUP (ORDER BY fine_amount)
        FILTER (WHERE fine_amount > 0))                             AS median_fine_allyears,
  max(fine_amount)                                                  AS max_fine,
  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
--  median_fine_allyears $14,576 · max_fine $713,795
--  earliest 2023-05-17 · latest 2026-04-17 · source_release 2026-05-01

-- ============================================================================
-- (2) HEADLINE: the size of the typical fine by year. Median is the headline
--     because it is a per-action statistic — unbiased by the partial 2023 and
--     2026 years. The median fine more than doubled across the window; across
--     the two COMPLETE years (2024, 2025) it rose 21%.
-- ============================================================================
SELECT
  extract(year FROM penalty_date)::int                              AS action_year,
  count(*) FILTER (WHERE penalty_type = 'Fine')                     AS fine_actions,
  round(percentile_cont(0.5) WITHIN GROUP (ORDER BY fine_amount)
        FILTER (WHERE fine_amount > 0))                             AS median_fine,
  round(avg(fine_amount) FILTER (WHERE fine_amount > 0))            AS avg_fine,
  round(sum(fine_amount) FILTER (WHERE penalty_type = 'Fine'))      AS fine_dollars
FROM public.cms_civil_money_penalties
GROUP BY action_year
ORDER BY action_year;
--  2023 (partial)  4,876  median $ 8,193  avg $24,038  $117,210,368
--  2024            5,098  median $16,055  avg $36,090  $183,989,128
--  2025            3,423  median $19,412  avg $41,888  $143,381,741
--  2026 (partial)    367  median $22,315  avg $40,209  $ 14,756,570
--  median 2025/2024 = 1.21x (+21%) · median 2026/2023 = 2.72x

-- ============================================================================
-- (3) The climb is the whole distribution, not a few outliers. Read the 25th,
--     50th, 75th and 90th percentiles of fine size per year: every percentile
--     rises, and the BOTTOM quartile (p25) more than tripled — the clearest sign
--     the entire schedule of amounts shifted up rather than a thin tail growing.
-- ============================================================================
SELECT
  extract(year FROM penalty_date)::int                              AS action_year,
  round(percentile_cont(0.25) WITHIN GROUP (ORDER BY fine_amount)
        FILTER (WHERE fine_amount > 0))                             AS p25,
  round(percentile_cont(0.50) WITHIN GROUP (ORDER BY fine_amount)
        FILTER (WHERE fine_amount > 0))                             AS p50,
  round(percentile_cont(0.75) WITHIN GROUP (ORDER BY fine_amount)
        FILTER (WHERE fine_amount > 0))                             AS p75,
  round(percentile_cont(0.90) WITHIN GROUP (ORDER BY fine_amount)
        FILTER (WHERE fine_amount > 0))                             AS p90
FROM public.cms_civil_money_penalties
WHERE penalty_type = 'Fine'
GROUP BY action_year
ORDER BY action_year;
--  2023  p25 $ 4,587  p50 $ 8,193  p75 $20,124  p90 $ 65,546
--  2024  p25 $ 8,824  p50 $16,055  p75 $43,220  p90 $ 92,167
--  2025  p25 $12,632  p50 $19,412  p75 $49,959  p90 $104,650
--  2026  p25 $15,185  p50 $22,315  p75 $37,410  p90 $ 91,022 (partial)
--  p25 2026/2023 = 3.31x; p25 2025/2023 = 2.75x

-- ============================================================================
-- (4) Two views of the same shift. The small-fine base THINNED: fines under
--     $10,000 fell from 55.3% of all fine actions in 2023 to 16.4% in 2025.
--     Meanwhile the dollar SHARE from fines of $100k+ held near 40% every year,
--     so the rise is the base moving up, not a thickening outlier tail.
-- ============================================================================
SELECT
  extract(year FROM penalty_date)::int                              AS action_year,
  count(*) FILTER (WHERE fine_amount > 0 AND fine_amount < 10000)   AS fines_under_10k,
  round(100.0 * count(*) FILTER (WHERE fine_amount > 0 AND fine_amount < 10000)
        / count(*) FILTER (WHERE fine_amount > 0), 1)               AS pct_under_10k,
  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
FROM public.cms_civil_money_penalties
WHERE penalty_type = 'Fine'
GROUP BY action_year
ORDER BY action_year;
--  2023  under_10k 2,698  55.3%  ·  100k+ 280  dollars 39.4%
--  2024  under_10k 1,482  29.1%  ·  100k+ 462  dollars 40.4%
--  2025  under_10k   562  16.4%  ·  100k+ 362  dollars 42.4%
--  2026  under_10k    11   3.0%  ·  100k+  34  dollars 39.4% (partial)

-- ============================================================================
-- (5) Quarter by quarter — the median fine rises near-monotonically, confirming
--     the annual trend is not an artifact of how the partial years split. The
--     step up is concentrated at the start of 2024. The final quarter (2026 Q2,
--     18 fines) is the file's trailing edge and is shown for completeness only.
-- ============================================================================
SELECT
  date_trunc('quarter', penalty_date)::date                         AS quarter,
  count(*) FILTER (WHERE penalty_type = 'Fine')                     AS fine_actions,
  round(percentile_cont(0.5) WITHIN GROUP (ORDER BY fine_amount)
        FILTER (WHERE fine_amount > 0))                             AS median_fine
FROM public.cms_civil_money_penalties
GROUP BY quarter
ORDER BY quarter;
--  2023Q2   998  $ 7,444   2024Q2 1,184 $16,830   2025Q2  951 $19,503
--  2023Q3 1,991  $ 8,469   2024Q3 1,183 $17,108   2025Q3  787 $19,135
--  2023Q4 1,887  $ 8,424   2024Q4 1,032 $16,801   2025Q4  550 $21,645
--  2024Q1 1,699  $13,674   2025Q1 1,135 $18,351   2026Q1  349 $22,320
--                                                  2026Q2   18 $21,568 (edge)

-- ============================================================================
-- (6) Counts decline at the tail — and why that is NOT a fall in enforcement.
--     The file is released 2026-05-01; recent actions report in with a lag, so
--     2025 and 2026 fine COUNTS are incomplete and understate true volume. This
--     query is the caution behind the study: it reads the SIZE trend (queries
--     2-5, all per-action) as robust, and treats annual counts as lag-affected.
-- ============================================================================
SELECT
  extract(year FROM penalty_date)::int                              AS action_year,
  count(*)                                                          AS actions,
  count(*) FILTER (WHERE penalty_type = 'Fine')                     AS fine_actions,
  count(*) FILTER (WHERE penalty_type = 'Payment Denial')           AS payment_denials,
  count(DISTINCT ccn)                                               AS facilities
FROM public.cms_civil_money_penalties
GROUP BY action_year
ORDER BY action_year;
--  2023 5,539  fines 4,876  denials   663  facilities 2,798 (partial, from 2023-05-17)
--  2024 6,170  fines 5,098  denials 1,072  facilities 3,819
--  2025 4,162  fines 3,423  denials   739  facilities 2,932 (lag-affected)
--  2026   406  fines   367  denials    39  facilities   371 (partial, to 2026-04-17)
