-- Hospitals running out of cash: the days-cash signal, and why most of it is a reporting artifact
-- Fonteum reproducible query — https://fonteum.com/research/hospitals-low-days-cash-on-hand
-- Data as of: 2026-05-24  ·  methodology: hcris-days-cash/v1
-- Source datasets: CMS Hospital Compare
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- Hospitals Low on Days Cash on Hand — fully reproducible query.
--
-- Source:   CMS HCRIS Hospital Cost Reports (form CMS-2552-10), FY2024 vintage.
-- Snapshot: cms-hcris-hospital-2552-10 / 2026-05-24
-- Archive:  https://downloads.cms.gov/FILES/HCRIS/HOSP10FY2024.ZIP
-- SHA-256:  e9552da36dcdcf904c5c4d35ffffe843af5b38afac196ac9db7db101bc8a96a0
--
-- Days Cash on Hand (All Sources) — per the American Hospital Directory's
-- published CMS-2552-10 worksheet reference:
--   (cash on hand + marketable securities + investments)
--     / ((total operating expense - depreciation) / 365)
WITH base AS (
  SELECT
    ccn,
    NULLIF(raw_payload->>'g000000_c1_l1','')::numeric                  AS cash,           -- Wksht G, line 1, col 1
    COALESCE(NULLIF(raw_payload->>'g000000_c1_l2','')::numeric, 0)
      + COALESCE(NULLIF(raw_payload->>'g000000_c1_l31','')::numeric, 0) AS investments,   -- G line 2 + line 31
    NULLIF(raw_payload->>'g300000_c1_l4','')::numeric                  AS total_oper_exp, -- Wksht G-3, line 4, col 1
    COALESCE(NULLIF(raw_payload->>'a700003_c9_l3','')::numeric, 0)     AS depreciation    -- Wksht A-7 Pt III, line 3, col 9
  FROM public.hcris_cost_reports_raw
),
dcoh AS (
  SELECT
    b.ccn,
    b.cash,
    365.0 * (b.cash + b.investments) / (b.total_oper_exp - b.depreciation) AS days_cash_on_hand,
    s.operating_margin_pct,
    left(b.ccn, 2) AS cms_state_code   -- first two CCN chars = CMS/SSA state code
  FROM base b
  JOIN public.hcris_facility_summary s ON s.ccn = b.ccn
  WHERE b.cash IS NOT NULL
    AND (b.total_oper_exp - b.depreciation) > 0
)
SELECT
  count(*)                                                                          AS computable,
  round(percentile_cont(0.5) WITHIN GROUP (ORDER BY days_cash_on_hand)::numeric, 1) AS median_days_cash,
  -- Naive, ARTIFACT-CONTAMINATED counts (do not headline these):
  count(*) FILTER (WHERE days_cash_on_hand < 30)                                    AS raw_under_30,
  count(*) FILTER (WHERE days_cash_on_hand < 30 AND cash < 100000)                  AS raw_under_30_near_zero_cash,
  -- Composite distress (defensible): credible cash AND operating loss AND low days:
  count(*) FILTER (WHERE cash >= 100000 AND operating_margin_pct < 0 AND days_cash_on_hand < 15) AS composite_under_15,
  count(*) FILTER (WHERE cash >= 100000 AND operating_margin_pct < 0 AND days_cash_on_hand < 30) AS composite_under_30,
  count(*) FILTER (WHERE cash >= 100000 AND operating_margin_pct < 0 AND days_cash_on_hand < 60) AS composite_under_60
FROM dcoh;
