America's care deserts are rural: two-thirds of U.S. health-care shortage areas

-- America's care deserts — fully reproducible query.
--
-- Question: of the areas the federal government has formally designated as
-- short of clinicians, how many are rural, how severe are they, and where do
-- they concentrate? We measure, over the ACTIVE (status = 'Designated')
-- universe, the rural share of HPSAs overall and by discipline, the national
-- designation totals, the HPSA score-severity distribution, and the per-state
-- ranking. The lead figure is the rural share of designated HPSAs (66.2%).
--
-- Source:
--   public.hrsa_shortage_areas — HRSA shortage-area designations, unified from
--     the data.hrsa.gov download files:
--       BCD_HPSA_FCT_DET_PC.csv  (Primary Care HPSAs)
--       BCD_HPSA_FCT_DET_MH.csv  (Mental Health HPSAs)
--       BCD_HPSA_FCT_DET_DH.csv  (Dental Health HPSAs)
--       MUA_DET.csv              (Medically Underserved Areas / Populations)
--     Snapshot 2026-06-14. 47,201 rows across all statuses; RLS Pattern B —
--     public read. License: US-Government-Works (17 U.S.C. §105).
--     methodology_version = 'hrsa-shortage-areas/v1'.
--
-- Universe: this study counts only rows whose status is 'Designated' — the
--   designations actually in force (25,281 rows). Withdrawn (18,931) and
--   Proposed-For-Withdrawal (2,989) rows are excluded. Every designation
--   carries a distinct designation_id, so no row is double-counted:
--   count(*) = count(distinct designation_id) = 25,281 over the universe.
--
-- These are simple counts over a 47k-row table, so every figure is EXACT as of
-- the snapshot, not estimated.

-- ============================================================================
-- (1) Headline: rural share of designated HPSAs, overall and by discipline.
--     The all-HPSA rural share (66.2%) is the lead figure.
-- ============================================================================
SELECT
  coalesce(discipline, 'ALL HPSA')                                   AS discipline,
  count(*)                                                           AS designated,
  count(*) FILTER (WHERE rural_status = 'Rural')                     AS rural,
  count(*) FILTER (WHERE rural_status = 'Non-Rural')                 AS non_rural,
  count(*) FILTER (WHERE rural_status = 'Partially Rural')           AS partially_rural,
  round(100.0 * count(*) FILTER (WHERE rural_status = 'Rural')
        / count(*), 1)                                               AS rural_pct
FROM public.hrsa_shortage_areas
WHERE status = 'Designated' AND designation_kind = 'hpsa'
GROUP BY ROLLUP (discipline)
ORDER BY discipline;
--  Primary Care    7,666  rural 5,058   66.0
--  Mental Health   6,413  rural 4,189   65.3
--  Dental Health   7,054  rural 4,752   67.4
--  ALL HPSA       21,133  rural 13,999  non_rural 6,069  partially 942   66.2
--  (123 designated HPSAs carry rural_status='Unknown' — reported separately,
--   not reassigned. 13,999 + 6,069 + 942 + 123 = 21,133.)

-- ============================================================================
-- (2) The MUA/MUP layer + the combined two-system total.
-- ============================================================================
SELECT
  designation_kind,
  count(*)                                              AS designated,
  count(*) FILTER (WHERE rural_status = 'Rural')        AS rural,
  round(100.0 * count(*) FILTER (WHERE rural_status = 'Rural')
        / count(*), 1)                                  AS rural_pct
FROM public.hrsa_shortage_areas
WHERE status = 'Designated'
GROUP BY ROLLUP (designation_kind)
ORDER BY designation_kind;
--  hpsa      21,133  rural 13,999  66.2
--  mua_mup    4,148  rural  2,432  58.6
--  TOTAL     25,281  rural 16,431  65.0   (every state + DC + territories = 60)

-- ============================================================================
-- (3) National designation totals by system + discipline (composition).
-- ============================================================================
SELECT
  designation_kind,
  discipline,
  count(*) AS designated
FROM public.hrsa_shortage_areas
WHERE status = 'Designated'
GROUP BY designation_kind, discipline
ORDER BY designated DESC;
--  hpsa     Primary Care   7,666
--  hpsa     Dental Health  7,054
--  hpsa     Mental Health  6,413
--  mua_mup  (null)         4,148
--  total designations 25,281 across 60 jurisdictions

-- ============================================================================
-- (4) Severity — HPSA score distribution (0-26 scale; higher = deeper need).
--     34.6% of designated HPSAs score 18+, the most-severe band.
-- ============================================================================
SELECT
  count(*)                                              AS designated_hpsa,
  count(*) FILTER (WHERE score >= 18)                   AS score_18_plus,
  count(*) FILTER (WHERE score BETWEEN 14 AND 17)       AS score_14_17,
  count(*) FILTER (WHERE score BETWEEN 1 AND 13)        AS score_1_13,
  count(*) FILTER (WHERE score = 0)                     AS score_0,
  round(100.0 * count(*) FILTER (WHERE score >= 18)
        / count(*), 1)                                  AS pct_18_plus
FROM public.hrsa_shortage_areas
WHERE status = 'Designated' AND designation_kind = 'hpsa';
--  designated 21,133 · 18+ 7,314 · 14-17 8,761 · 1-13 5,043 · 0 15 · pct_18+ 34.6

-- Mean / median score by discipline (severity order flips vs raw count):
SELECT
  discipline,
  count(*)                                                       AS designated,
  round(avg(score), 2)                                           AS mean_score,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY score)             AS median_score,
  max(score)                                                     AS max_score
FROM public.hrsa_shortage_areas
WHERE status = 'Designated' AND designation_kind = 'hpsa'
GROUP BY discipline
ORDER BY mean_score DESC;
--  Dental Health  7,054  mean 16.53  median 17  max 26
--  Mental Health  6,413  mean 16.36  median 17  max 25
--  Primary Care   7,666  mean 15.09  median 16  max 25

-- ============================================================================
-- (5) Where the deserts concentrate — top states by designated HPSAs.
-- ============================================================================
SELECT
  state,
  count(*) FILTER (WHERE discipline = 'Primary Care')   AS primary_care,
  count(*) FILTER (WHERE discipline = 'Mental Health')  AS mental_health,
  count(*) FILTER (WHERE discipline = 'Dental Health')  AS dental,
  count(*)                                              AS total_hpsa
FROM public.hrsa_shortage_areas
WHERE status = 'Designated' AND designation_kind = 'hpsa'
GROUP BY state
ORDER BY total_hpsa DESC
LIMIT 7;
--  CA  559  518  546  1,623
--  TX  390  386  335  1,111
--  AK  330  330  328    988   (almost entirely rural — statewide care desert)
--  MO  340  259  326    925
--  MI  274  257  262    793
--  IL  295  184  250    729
--  KY  245  210  246    701

-- ============================================================================
-- (6) Universe reconciliation — status split + distinct-id check.
-- ============================================================================
SELECT
  count(*)                                              AS all_rows,
  count(*) FILTER (WHERE status = 'Designated')         AS designated,
  count(DISTINCT designation_id)
    FILTER (WHERE status = 'Designated')                AS distinct_designated_ids,
  count(*) FILTER (WHERE status = 'Withdrawn')          AS withdrawn,
  count(*) FILTER (WHERE status = 'Proposed For Withdrawal') AS proposed_withdrawal,
  count(DISTINCT state) FILTER (WHERE status = 'Designated') AS jurisdictions
FROM public.hrsa_shortage_areas;
--  all_rows 47,201 · designated 25,281 · distinct_designated_ids 25,281
--  withdrawn 18,931 · proposed 2,989 · jurisdictions 60
--  (25,281 + 18,931 + 2,989 = 47,201; designated = distinct designation_id,
--   so no duplicate inflation.)