Most U.S. health-care shortage areas are a single facility, not a community

-- Most U.S. health-care shortage areas are not communities — they are single
-- facilities and special populations. Fully reproducible query.
--
-- Question: when the federal government designates a Health Professional
-- Shortage Area (HPSA), what is actually being designated — a whole community,
-- a specific low-income population, or one safety-net facility? Read the active
-- HPSA roster by designation TYPE and one fact dominates: only 10.9% of
-- designated HPSAs are whole geographies. 68.8% are a single facility — a rural
-- clinic, a community health center, a tribal health site, or a prison — and
-- 20.3% are a named population group. A HPSA designation is a coverage and
-- workforce-policy instrument, NOT a quality, fraud, or wrongdoing signal of any
-- kind, and it names no individual clinician.
--
-- 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-16. 47,201 rows across all statuses; public, read-only.
--     License: US-Government-Works (17 U.S.C. Sec. 105).
--     methodology_version = 'hrsa-shortage-areas/v1'.
--
-- Universe: this study counts only the HPSA rows whose status is 'Designated'
--   AND designation_kind = 'hpsa' — the HPSA designations actually in force
--   (21,133 rows). Withdrawn and Proposed-For-Withdrawal rows are excluded, as
--   is the broader MUA/MUP layer. Every designation carries a distinct
--   designation_id, so no row is double-counted:
--   count(*) = count(distinct designation_id) = 21,133 over the universe.
--
-- These are simple counts over a 47k-row table, so every figure is EXACT as of
-- the snapshot, not estimated.

-- ============================================================================
-- (1) Universe reconciliation — the designated HPSA file at a glance.
-- ============================================================================
SELECT
  count(*)                                                          AS all_rows,
  count(*) FILTER (WHERE status = 'Designated'
                     AND designation_kind = 'hpsa')                 AS designated_hpsa,
  count(DISTINCT designation_id) FILTER (WHERE status = 'Designated'
                     AND designation_kind = 'hpsa')                 AS distinct_ids,
  count(DISTINCT designation_type) FILTER (WHERE status = 'Designated'
                     AND designation_kind = 'hpsa')                 AS type_count,
  max(last_update_date)                                             AS latest_update,
  count(DISTINCT state) FILTER (WHERE status = 'Designated')        AS jurisdictions
FROM public.hrsa_shortage_areas;
--  all_rows 47,201 · designated_hpsa 21,133 · distinct_ids 21,133
--  type_count 10 · latest_update 2026-06-09 · jurisdictions 60
--  (designated_hpsa = distinct designation_id, so no duplicate inflation.)

-- ============================================================================
-- (2) HEADLINE: the structural split. Each designated HPSA is one of three
--     things — a whole GEOGRAPHY, a named POPULATION group, or a single
--     FACILITY. Facilities dominate (68.8%); whole geographies are only 10.9%.
--     The facility group's mean score is the highest of the three, and its
--     median (17) is two points above the geographic median (14).
-- ============================================================================
WITH c AS (
  SELECT
    CASE
      WHEN designation_type IN ('Geographic HPSA','High Needs Geographic HPSA')
        THEN 'geographic'
      WHEN designation_type = 'HPSA Population'
        THEN 'population'
      ELSE 'facility'
    END                                                             AS structure,
    score
  FROM public.hrsa_shortage_areas
  WHERE status = 'Designated' AND designation_kind = 'hpsa'
)
SELECT
  structure,
  count(*)                                                          AS designated,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1)                AS pct_of_all,
  round(avg(score), 2)                                             AS mean_score,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY score)               AS median_score
FROM c
GROUP BY structure
ORDER BY designated DESC;
--  facility    14,538  68.8%  mean 16.28  median 17
--  population   4,295  20.3%  mean 15.92  median 16
--  geographic   2,300  10.9%  mean 13.94  median 14
--  (14,538 + 4,295 + 2,300 = 21,133 = the whole designated HPSA universe.)

-- ============================================================================
-- (3) WHAT the facilities are — the facility group broken out by type, with
--     severity. Rural Health Clinics, FQHCs, and tribal health sites are the
--     "automatic" safety-net designations; FQHCs are the deepest-need band
--     (mean 19.67; 3,037 of 4,053 score 18+). Correctional facilities are the
--     largest non-safety-net facility block and the lowest-scoring (mean 10.43).
-- ============================================================================
SELECT
  designation_type,
  count(*)                                                          AS designated,
  round(avg(score), 2)                                             AS mean_score,
  count(*) FILTER (WHERE score >= 18)                              AS score_18_plus
FROM public.hrsa_shortage_areas
WHERE status = 'Designated' AND designation_kind = 'hpsa'
  AND designation_type NOT IN
    ('Geographic HPSA','High Needs Geographic HPSA','HPSA Population')
GROUP BY designation_type
ORDER BY designated DESC;
--  Rural Health Clinic                          5,505  mean 15.68  18+   873
--  Federally Qualified Health Center            4,053  mean 19.67  18+ 3,037
--  Indian Health Service / Tribal / Urban       2,784  mean 15.76  18+ 1,230
--  Correctional Facility                        1,605  mean 10.43  18+   257
--  Federally Qualified Health Center Look A Like   513  mean 17.58  18+   262
--  Other Facility                                  44  mean 15.77  18+    13
--  State Mental Hospital                            34  mean 10.59  18+     3
--  (sum = 14,538; safety-net auto group RHC+FQHC+FQHC-LAL+IHS = 12,855 = 60.8%
--   of every designated HPSA on file.)

-- ============================================================================
-- (4) The structural split BY DISCIPLINE. Mental-health shortage is the most
--     facility-bound of the three care types (76.7% facility), primary care the
--     least (63.2%). Whole-geography designations are rarest for mental health.
-- ============================================================================
WITH c AS (
  SELECT discipline,
    CASE
      WHEN designation_type IN ('Geographic HPSA','High Needs Geographic HPSA')
        THEN 'geographic'
      WHEN designation_type = 'HPSA Population' THEN 'population'
      ELSE 'facility'
    END                                                             AS structure
  FROM public.hrsa_shortage_areas
  WHERE status = 'Designated' AND designation_kind = 'hpsa'
)
SELECT
  discipline,
  count(*) FILTER (WHERE structure = 'facility')                   AS facility,
  count(*) FILTER (WHERE structure = 'population')                 AS population,
  count(*) FILTER (WHERE structure = 'geographic')                 AS geographic,
  count(*)                                                         AS total,
  round(100.0 * count(*) FILTER (WHERE structure = 'facility')
        / count(*), 1)                                             AS facility_pct
FROM c
GROUP BY discipline
ORDER BY total DESC;
--  Primary Care   facility 4,844  pop 1,825  geo 997  total 7,666  63.2%
--  Dental Health  facility 4,777  pop 1,814  geo 463  total 7,054  67.7%
--  Mental Health  facility 4,917  pop   656  geo 840  total 6,413  76.7%

-- ============================================================================
-- (5) The correctional layer, on its own. 1,605 designated HPSAs are prisons
--     and jails; mental health is the largest discipline among them (576) and
--     the highest-scoring (mean 13.27). Most are rural. This is the clearest
--     case of a HPSA that is plainly NOT a community.
-- ============================================================================
SELECT
  discipline,
  count(*)                                                          AS designated,
  round(avg(score), 2)                                             AS mean_score,
  count(*) FILTER (WHERE rural_status = 'Rural')                   AS rural
FROM public.hrsa_shortage_areas
WHERE status = 'Designated' AND designation_kind = 'hpsa'
  AND designation_type = 'Correctional Facility'
GROUP BY discipline
ORDER BY designated DESC;
--  Mental Health  576  mean 13.27  rural 339
--  Primary Care   550  mean  9.36  rural 328
--  Dental Health  479  mean  8.24  rural 279
--  (576 + 550 + 479 = 1,605 correctional-facility HPSAs.)

-- ============================================================================
-- (6) Full designation_type composition (designated HPSA only) — the raw
--     fourteen-into-three roll-up behind blocks (2)-(3). Geographic types are
--     the two "*Geographic HPSA" labels; population is the single "HPSA
--     Population"; everything else is a facility.
-- ============================================================================
SELECT
  designation_type,
  count(*)                                                          AS designated,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1)                AS pct_of_all
FROM public.hrsa_shortage_areas
WHERE status = 'Designated' AND designation_kind = 'hpsa'
GROUP BY designation_type
ORDER BY designated DESC;
--  Rural Health Clinic                          5,505  26.1%
--  HPSA Population                               4,295  20.3%
--  Federally Qualified Health Center            4,053  19.2%
--  Indian Health Service / Tribal / Urban       2,784  13.2%
--  Correctional Facility                        1,605   7.6%
--  Geographic HPSA                              1,554   7.4%
--  High Needs Geographic HPSA                     746   3.5%
--  Federally Qualified Health Center Look A Like   513   2.4%
--  Other Facility                                  44   0.2%
--  State Mental Hospital                           34   0.2%
--  (geographic 1,554 + 746 = 2,300 (10.9%); facility = the other seven labels
--   = 14,538 (68.8%); population = 4,295 (20.3%).)