-- Hospitals at Risk of Closing? The Days-Cash-on-Hand Signal
-- Fonteum reproducible query — https://fonteum.com/research/hospitals-at-risk-of-closing
-- 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)
-- Audit observation: 2026-07-14. Source snapshot remains 2026-05-24.
-- audit-claim: liquidity-overview
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 < 15)                                    AS raw_under_15,
  count(*) FILTER (WHERE days_cash_on_hand < 60)                                    AS raw_under_60,
  count(*) FILTER (WHERE days_cash_on_hand < 30 AND cash < 100000)                  AS raw_under_30_near_zero_cash,
  count(*) FILTER (WHERE cash >= 100000)                                             AS credible_cash_reporters,
  -- 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;

-- audit-claim: days-cash-distribution
WITH base AS (
  SELECT
    ccn,
    NULLIF(raw_payload->>'g000000_c1_l1','')::numeric AS cash,
    COALESCE(NULLIF(raw_payload->>'g000000_c1_l2','')::numeric, 0)
      + COALESCE(NULLIF(raw_payload->>'g000000_c1_l31','')::numeric, 0) AS investments,
    NULLIF(raw_payload->>'g300000_c1_l4','')::numeric AS total_oper_exp,
    COALESCE(NULLIF(raw_payload->>'a700003_c9_l3','')::numeric, 0) AS depreciation
  FROM public.hcris_cost_reports_raw
), dcoh AS (
  SELECT
    365.0 * (cash + investments) / (total_oper_exp - depreciation) AS days_cash_on_hand
  FROM base
  WHERE cash IS NOT NULL
    AND (total_oper_exp - depreciation) > 0
)
SELECT
  count(*) AS total,
  round(percentile_cont(0.5) WITHIN GROUP (ORDER BY days_cash_on_hand)::numeric, 1) AS median_days,
  count(*) FILTER (WHERE days_cash_on_hand < 0) AS under_zero,
  count(*) FILTER (WHERE days_cash_on_hand >= 0 AND days_cash_on_hand < 15) AS zero_to_15,
  count(*) FILTER (WHERE days_cash_on_hand >= 15 AND days_cash_on_hand < 30) AS fifteen_to_30,
  count(*) FILTER (WHERE days_cash_on_hand >= 30 AND days_cash_on_hand < 60) AS thirty_to_60,
  count(*) FILTER (WHERE days_cash_on_hand >= 60 AND days_cash_on_hand < 120) AS sixty_to_120,
  count(*) FILTER (WHERE days_cash_on_hand >= 120 AND days_cash_on_hand < 200) AS one_twenty_to_200,
  count(*) FILTER (WHERE days_cash_on_hand >= 200) AS two_hundred_plus
FROM dcoh;

-- audit-claim: composite-distress-states
WITH base AS (
  SELECT
    ccn,
    NULLIF(raw_payload->>'g000000_c1_l1','')::numeric AS cash,
    COALESCE(NULLIF(raw_payload->>'g000000_c1_l2','')::numeric, 0)
      + COALESCE(NULLIF(raw_payload->>'g000000_c1_l31','')::numeric, 0) AS investments,
    NULLIF(raw_payload->>'g300000_c1_l4','')::numeric AS total_oper_exp,
    COALESCE(NULLIF(raw_payload->>'a700003_c9_l3','')::numeric, 0) AS depreciation
  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
  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
  left(ccn, 2) AS cms_state_code,
  count(*) AS composite_distress_hospitals
FROM dcoh
WHERE cash >= 100000
  AND operating_margin_pct < 0
  AND days_cash_on_hand < 30
GROUP BY left(ccn, 2)
ORDER BY composite_distress_hospitals DESC, cms_state_code;

-- audit-claim: composite-distress-chart-vector
WITH base AS (
  SELECT
    ccn,
    NULLIF(raw_payload->>'g000000_c1_l1','')::numeric AS cash,
    COALESCE(NULLIF(raw_payload->>'g000000_c1_l2','')::numeric, 0)
      + COALESCE(NULLIF(raw_payload->>'g000000_c1_l31','')::numeric, 0) AS investments,
    NULLIF(raw_payload->>'g300000_c1_l4','')::numeric AS total_oper_exp,
    COALESCE(NULLIF(raw_payload->>'a700003_c9_l3','')::numeric, 0) AS depreciation
  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
  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
), by_state AS (
  SELECT
    left(ccn, 2) AS cms_state_code,
    count(*) AS composite_distress_hospitals
  FROM dcoh
  WHERE cash >= 100000
    AND operating_margin_pct < 0
    AND days_cash_on_hand < 30
  GROUP BY left(ccn, 2)
)
SELECT
  cms_state_code,
  composite_distress_hospitals,
  sum(composite_distress_hospitals) OVER () AS nationwide
FROM by_state
ORDER BY composite_distress_hospitals DESC, cms_state_code
LIMIT 15;

-- audit-claim: liquidity-examples
WITH base AS (
  SELECT
    ccn,
    NULLIF(raw_payload->>'g000000_c1_l1','')::numeric AS cash,
    COALESCE(NULLIF(raw_payload->>'g000000_c1_l2','')::numeric, 0)
      + COALESCE(NULLIF(raw_payload->>'g000000_c1_l31','')::numeric, 0) AS investments,
    NULLIF(raw_payload->>'g300000_c1_l4','')::numeric AS total_oper_exp,
    COALESCE(NULLIF(raw_payload->>'a700003_c9_l3','')::numeric, 0) AS depreciation
  FROM public.hcris_cost_reports_raw
), dcoh AS (
  SELECT
    *,
    365.0 * (cash + investments) / (total_oper_exp - depreciation) AS days_cash_on_hand
  FROM base
  WHERE cash IS NOT NULL
    AND (total_oper_exp - depreciation) > 0
)
SELECT
  ccn,
  cash,
  total_oper_exp,
  round(days_cash_on_hand::numeric, 1) AS days_cash_on_hand
FROM dcoh
WHERE ccn IN ('330154', '440039', '330214', '360180', '050441', '450076')
ORDER BY ccn;
