-- Where the uninsured land: coverage at America's community health centers, 2024
-- Fonteum reproducible query — https://fonteum.com/research/community-health-center-safety-net-2024
-- Data as of: 2026-06-15  ·  methodology: community-health-center-safety-net/v1
-- Source datasets: HRSA UDS
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- Who pays at America's community health centers — the safety-net coverage
-- mix at HRSA-funded Federally Qualified Health Centers (FQHCs), 2024.
-- Fully reproducible query.
--
-- Question: of the people who use a federally funded community health center,
-- what share are uninsured or covered by Medicaid (the "safety-net" share),
-- and how does that mix vary by state? The lead finding is the STATE FLIP:
-- community health centers everywhere serve a roughly two-thirds safety-net
-- population, but in some states that shows up as uninsured patients and in
-- others as Medicaid patients. Texas: 33.6% uninsured vs 29.8% Medicaid.
-- California: 10.1% uninsured vs 72.0% Medicaid.
--
-- Sources (public.* — HRSA Uniform Data System, snapshot release 2026-06-15):
--   public.hrsa_uds_awardees           -- 1,359 Health Center Program awardees
--     (grantees), grant_year = 2024. total_patients, medicaid_patients,
--     uninsured_patients, total_fte, clinical_fte, total_sites, state.
--   public.hrsa_uds_sites              -- 16,334 service-delivery sites.
--   public.hrsa_uds_quality_measures   -- 18,046 rows, 14 clinical measures.
--   Authority: Health Resources and Services Administration (HRSA), data.hrsa.gov.
--   Tier-1 research-only. License: US-Government-Works (17 U.S.C. Sec. 105).
--   methodology_version = 'community-health-center-safety-net/v1'.
--
-- Universe: this study reads grant_year = 2024 only — a single annual reporting
--   period, point-in-time, not a trend. UDS is annual, self-reported program
--   data; figures are aggregate at the awardee/state/national level. This
--   release carries NO NPI and NO CCN on any site row, so there is no
--   provider-identity or entity link — every number is a count or share of an
--   aggregate, and no individual awardee, site, or clinician is named.
--
-- Safety-net definition: a patient is "safety-net" if uninsured OR enrolled in
--   Medicaid. UDS reports patient counts by primary medical insurance; Medicaid
--   and uninsured are two of those categories. The two groups are disjoint in
--   the UDS primary-insurance tally, so safety_net = medicaid + uninsured.

-- ============================================================================
-- (1) NATIONAL coverage mix, 2024. Shares are computed from unrounded patient
--     totals (sum of numerators / sum of denominators), never from rounded
--     per-awardee percentages.
-- ============================================================================
SELECT
  count(*)                                                              AS awardees,
  sum(total_patients)                                                  AS total_patients,
  sum(medicaid_patients)                                               AS medicaid_patients,
  sum(uninsured_patients)                                              AS uninsured_patients,
  sum(medicaid_patients) + sum(uninsured_patients)                     AS safety_net_patients,
  round(100.0 * sum(medicaid_patients)  / nullif(sum(total_patients),0), 1) AS medicaid_pct,
  round(100.0 * sum(uninsured_patients) / nullif(sum(total_patients),0), 1) AS uninsured_pct,
  round(100.0 * (sum(medicaid_patients) + sum(uninsured_patients))
        / nullif(sum(total_patients),0), 1)                            AS safety_net_pct
FROM public.hrsa_uds_awardees
WHERE grant_year = 2024;
--  awardees 1,359 · total_patients 32,387,774
--  medicaid 15,598,866 (48.2%) · uninsured 5,857,356 (18.1%)
--  safety_net 21,456,222 (66.2%)
--  (Medicaid and uninsured shares are rounded independently; the precise
--   combined safety-net share over unrounded totals is 66.2%.)

-- ============================================================================
-- (2) AWARDEE-LEVEL concentration. How many of the 1,359 awardees draw a
--     MAJORITY of their patients from the safety-net (uninsured + Medicaid)
--     population, and how many serve a majority-UNINSURED panel?
-- ============================================================================
SELECT
  count(*)                                                             AS awardees,
  count(*) FILTER (
    WHERE (medicaid_patients + uninsured_patients)::numeric
          / nullif(total_patients,0) >= 0.5)                           AS majority_safety_net,
  count(*) FILTER (
    WHERE uninsured_patients::numeric
          / nullif(total_patients,0) >= 0.5)                           AS majority_uninsured
FROM public.hrsa_uds_awardees
WHERE grant_year = 2024;
--  awardees 1,359 · majority_safety_net 1,021 (75.1%) · majority_uninsured 76

-- ============================================================================
-- (3) STATE coverage mix — the FLIP. Ordered by uninsured share, descending.
--     Limited to states/territories with >= 100,000 health-center patients so
--     small-denominator noise does not lead the ranking. Uninsured share and
--     Medicaid share move in opposite directions across states.
-- ============================================================================
SELECT
  state,
  count(*)                                                             AS awardees,
  sum(total_patients)                                                  AS patients,
  round(100.0 * sum(uninsured_patients) / nullif(sum(total_patients),0), 1) AS uninsured_pct,
  round(100.0 * sum(medicaid_patients)  / nullif(sum(total_patients),0), 1) AS medicaid_pct
FROM public.hrsa_uds_awardees
WHERE grant_year = 2024
GROUP BY state
HAVING sum(total_patients) > 100000
ORDER BY uninsured_pct DESC;
--  Highest uninsured share:
--    UT 43.5% / 18.6% Medicaid · TX 33.6% / 29.8% · TN 32.7% / 27.8%
--    NE 32.7% / 38.5% · MN 31.8% / 42.8% · NC 29.9% / 28.8% · NV 29.7% / 37.3%
--  Highest Medicaid share (tail of the same ranking):
--    CA 72.0% / 10.1% uninsured · PR 61.4% / 9.8% · OR 61.1% / 8.9%
--    CT 58.4% / 15.3% · WI 57.8% / 16.8% · HI 55.9% / 9.2% · IL 55.5% / 19.0%
--    WA 55.1% / 13.5%

-- ============================================================================
-- (4) HEADLINE pair: Texas vs California, 2024. Same ~two-thirds safety-net
--     reliance, opposite coverage composition. Note the absolute count:
--     Texas's health centers serve MORE uninsured patients than California's
--     (624,613 vs 582,340) despite California serving 3x the total patients.
-- ============================================================================
SELECT
  state,
  sum(total_patients)                                                  AS patients,
  sum(uninsured_patients)                                              AS uninsured,
  sum(medicaid_patients)                                               AS medicaid,
  round(100.0 * sum(uninsured_patients) / nullif(sum(total_patients),0), 1) AS uninsured_pct,
  round(100.0 * sum(medicaid_patients)  / nullif(sum(total_patients),0), 1) AS medicaid_pct
FROM public.hrsa_uds_awardees
WHERE grant_year = 2024 AND state IN ('TX','CA')
GROUP BY state;
--  CA 5,787,948 patients · 582,340 uninsured (10.1%) · 4,168,857 Medicaid (72.0%)
--  TX 1,859,052 patients · 624,613 uninsured (33.6%) ·   553,082 Medicaid (29.8%)

-- ============================================================================
-- (5) SPREAD + inverse relationship across the 48 states/territories with
--     >= 100,000 patients: min/max uninsured share and the Pearson correlation
--     between a state's uninsured share and its Medicaid share.
-- ============================================================================
WITH s AS (
  SELECT
    state,
    100.0 * sum(uninsured_patients) / nullif(sum(total_patients),0) AS u,
    100.0 * sum(medicaid_patients)  / nullif(sum(total_patients),0) AS m
  FROM public.hrsa_uds_awardees
  WHERE grant_year = 2024
  GROUP BY state
  HAVING sum(total_patients) > 100000
)
SELECT
  count(*)                       AS states_over_100k,
  round(min(u), 1)               AS min_uninsured_pct,   -- ME 8.6
  round(max(u), 1)               AS max_uninsured_pct,   -- UT 43.5
  round(corr(u, m)::numeric, 2)  AS corr_uninsured_medicaid  -- -0.52
FROM s;
--  states_over_100k 48 · min 8.6 (ME) · max 43.5 (UT) · corr -0.52

-- ============================================================================
-- (6) Footprint + workforce context. Sites, FTE staffing, patients per FTE.
-- ============================================================================
SELECT
  (SELECT count(*) FROM public.hrsa_uds_sites WHERE grant_year = 2024)  AS sites,
  sum(total_sites)                                                     AS reported_sites,
  round(sum(total_fte), 0)                                             AS total_fte,
  round(sum(clinical_fte), 0)                                          AS clinical_fte,
  round(sum(total_patients)::numeric / nullif(sum(total_fte),0), 0)    AS patients_per_fte
FROM public.hrsa_uds_awardees
WHERE grant_year = 2024;
--  sites 16,334 · total_fte 152,677 · clinical_fte 51,330 · patients_per_fte 212

-- ============================================================================
-- (7) CLINICAL-QUALITY context — patient-weighted national rate per measure
--     (sum of numerators / sum of denominators) across reporting awardees.
--     national_avg is null for every row in this release, so no HRSA benchmark
--     comparison is possible; rates are reported as-is, not graded.
-- ============================================================================
SELECT
  measure_name,
  round(100.0 * sum(numerator) / nullif(sum(denominator),0), 1)         AS weighted_rate_pct,
  count(*) FILTER (WHERE rate IS NOT NULL)                              AS awardees_reporting
FROM public.hrsa_uds_quality_measures
WHERE grant_year = 2024
GROUP BY measure_name
ORDER BY weighted_rate_pct DESC NULLS LAST;
--  Tobacco screening & cessation        84.2% (1,354 awardees)
--  Statin therapy, cardiovascular       78.2% (1,356)
--  Depression screening & follow-up     73.7% (1,358)
--  Cervical cancer screening            55.4% (1,356)
--  Colorectal cancer screening          42.7% (1,353)
--  Childhood immunization status        27.5% (  887 — thinnest-reported measure)

-- ============================================================================
-- (8) LIMITATIONS check — this release carries no provider identity. Confirm
--     that the site table holds ZERO non-null NPI and ZERO non-null CCN, so no
--     entity/provider link is possible from this data.
-- ============================================================================
SELECT
  count(*)                                                             AS site_rows,
  count(npi) FILTER (WHERE npi IS NOT NULL AND npi <> '')              AS rows_with_npi,
  count(ccn) FILTER (WHERE ccn IS NOT NULL AND ccn <> '')              AS rows_with_ccn
FROM public.hrsa_uds_sites
WHERE grant_year = 2024;
--  site_rows 16,334 · rows_with_npi 0 · rows_with_ccn 0
--  (UDS in this release is aggregate program data — no NPI, no CCN, no entity
--   link. national_avg and grant_amount_usd are likewise null in this snapshot.)
