America's medically underserved areas are a map drawn last century

-- America's Medically Underserved Areas and Populations (MUA/MUP) are an old
-- map: most of the active designations were made last century and have never
-- been updated since. Fully reproducible query.
--
-- Question: HRSA's Medically Underserved Area / Population (MUA/MUP) program is
-- a SEPARATE designation system from the Health Professional Shortage Area
-- (HPSA) program. An MUA/MUP is scored once on the Index of Medical Underservice
-- (IMU) — a 0-to-100 scale combining primary-care physicians per 1,000 people,
-- the infant mortality rate, the share of population below poverty, and the
-- share age 65 and older — and a service area qualifies at an IMU of 62.0 or
-- below. Unlike many HPSA designations, an MUA/MUP carries NO fixed expiration:
-- it stays in effect until HRSA actively withdraws it. So how old is the active
-- map, and how current is the index behind it?
--
-- The lead figure: of the 4,148 currently DESIGNATED MUA/MUP rows, 2,991 (72.1%)
-- were designated before 2000, the median designation dates to May 1994, and
-- 1,074 of them — one in four — share a single founding-cohort date of
-- 1978-11-01. A designation is a workforce-policy instrument, NOT a quality,
-- fraud, or wrongdoing signal of any kind, and names no individual provider.
--
-- Source:
--   public.hrsa_shortage_areas, rows WHERE designation_kind = 'mua_mup' — HRSA's
--     Medically Underserved Areas/Populations file, published via data.hrsa.gov.
--     4,903 MUA/MUP rows; snapshot last_update_date max 2026-05-28. Public,
--     read-only. License: US-Government-Works (17 U.S.C. Sec. 105).
--     methodology_version = 'hrsa-mua-mup/v1'.
--
-- Universe: this study reads the ACTIVE designations — status = 'Designated'
--   (4,148 rows) — the current MUA/MUP map. Withdrawn (673) and proposed-for-
--   withdrawal (82) rows are reconciled in query (1) but excluded from the
--   active-map figures. The same active-designation universe is used by the
--   companion HRSA shortage-area studies.
--
-- IMU note: the published IMU is stored in `score`. 270 active designations
--   carry score = 0, which we read as "no IMU published" (nearly all are pre-2010
--   designations) and EXCLUDE from IMU averages — we never treat a missing score
--   as a literal zero. A higher IMU means LESS underservice; 62.0 is the cutoff.

-- ============================================================================
-- (1) Universe reconciliation — the MUA/MUP file at a glance.
-- ============================================================================
SELECT
  count(*)                                                          AS mua_mup_rows,
  count(*) FILTER (WHERE status = 'Designated')                     AS designated,
  count(*) FILTER (WHERE status = 'Withdrawn')                      AS withdrawn,
  count(*) FILTER (WHERE status = 'Proposed For Withdrawal')        AS proposed_withdrawal,
  min(designation_date)                                            AS earliest_designation,
  max(designation_date)                                            AS latest_designation,
  max(last_update_date)                                            AS snapshot
FROM public.hrsa_shortage_areas
WHERE designation_kind = 'mua_mup';
--  mua_mup_rows 4,903 · designated 4,148 · withdrawn 673 · proposed_withdrawal 82
--  earliest_designation 1977-03-11 · latest_designation 2026-05-28 · snapshot 2026-05-28

-- ============================================================================
-- (2) HEADLINE: how OLD the active map is. Decade of designation, plus the
--     share predating 2000 and the share from the last decade. 72.1% predate
--     2000; the median active designation dates to 1994-05-11; only 7.8% were
--     designated in the last ten years.
-- ============================================================================
WITH d AS (
  SELECT * FROM public.hrsa_shortage_areas
  WHERE designation_kind = 'mua_mup' AND status = 'Designated'
)
SELECT
  (floor(extract(year FROM designation_date) / 10) * 10)::int || 's'  AS decade,
  count(*)                                                          AS designations,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1)                AS pct_of_active
FROM d
GROUP BY decade
ORDER BY decade;
--  1970s  1,090  26.3%
--  1980s    323   7.8%
--  1990s  1,578  38.0%
--  2000s    613  14.8%
--  2010s    387   9.3%
--  2020s    157   3.8%
--
-- Summary figures over the same active set (4,148 rows):
--   designated before 2000-01-01 ............... 2,991 (72.1%)
--   designated 2016-01-01 onward (last decade) ...  324 ( 7.8%)
--   median designation_date .................... 1994-05-11
--   designations whose last_update_date equals
--     their original designation_date .......... 3,030 (73.0% — no recorded change since)
--   median last_update_date .................... 1994-06-03

-- ============================================================================
-- (3) The 1978 founding cohort. A single designation date — November 1, 1978 —
--     accounts for 1,074 of the 4,148 active designations (25.9%): one in four
--     of today's medically underserved areas was designated on one day, 47 years
--     ago, and is still in effect. 718 of those 1,074 have never been updated since.
-- ============================================================================
WITH d AS (
  SELECT * FROM public.hrsa_shortage_areas
  WHERE designation_kind = 'mua_mup' AND status = 'Designated'
)
SELECT
  designation_date,
  count(*)                                                          AS designations,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1)                AS pct_of_active
FROM d
GROUP BY designation_date
ORDER BY designations DESC
LIMIT 6;
--  1978-11-01  1,074  25.9%   <- the founding cohort, still designated
--  1994-05-11    309   7.4%
--  1994-05-12    285   6.9%
--  1994-05-04     58   1.4%
--  1994-04-12     44   1.1%
--  1994-05-09     34   0.8%

-- ============================================================================
-- (4) The Index of Medical Underservice behind the active map. Among the 3,808
--     standard (non-exception) designations that carry a published IMU, the
--     average is 54.8 on the 0-to-100 scale where 62.0 or below qualifies; 2,313
--     of them sit in the narrow 55-62 band just under the cutoff. The IMU is
--     computed once at designation and is not refreshed, so for a file whose
--     median designation is 1994 these are largely 1990s index values. 270 active
--     designations carry no published IMU at all (score = 0) and are excluded here.
-- ============================================================================
WITH d AS (
  SELECT * FROM public.hrsa_shortage_areas
  WHERE designation_kind = 'mua_mup' AND status = 'Designated'
    AND designation_type NOT LIKE '%Exception%'
    AND score > 0
)
SELECT
  CASE
    WHEN score <= 40 THEN 'a. IMU <= 40 (deepest need)'
    WHEN score <= 50 THEN 'b. IMU 40-50'
    WHEN score <= 55 THEN 'c. IMU 50-55'
    WHEN score <= 62 THEN 'd. IMU 55-62 (just under cutoff)'
    ELSE                   'e. IMU > 62 (above cutoff)'
  END                                                               AS imu_band,
  count(*)                                                          AS designations
FROM d
GROUP BY imu_band
ORDER BY imu_band;
--  a. IMU <= 40 (deepest need) ......  166
--  b. IMU 40-50 .....................  546
--  c. IMU 50-55 .....................  777
--  d. IMU 55-62 (just under cutoff) . 2,313   <- 60.8% of scored standard designations
--  e. IMU > 62 (above cutoff) .......    6
--  (mean IMU across these 3,808 scored standard designations = 54.8; range 18.1-74.7.)

-- ============================================================================
-- (5) The Governor's Exception. 216 active designations (140 areas + 76
--     populations) were made despite not qualifying on the index — at a state
--     governor's request under HRSA's exception process. Among the 70 of them
--     that carry a published IMU, the average is 72.0, above the 62.0 cutoff,
--     exactly as the exception is designed to allow.
-- ============================================================================
WITH d AS (
  SELECT
    (designation_type LIKE '%Exception%')                          AS is_governors_exception,
    score
  FROM public.hrsa_shortage_areas
  WHERE designation_kind = 'mua_mup' AND status = 'Designated'
)
SELECT
  is_governors_exception,
  count(*)                                                          AS designations,
  count(*) FILTER (WHERE score > 0)                                 AS with_published_imu,
  round(avg(score) FILTER (WHERE score > 0), 1)                     AS avg_imu_scored
FROM d
GROUP BY is_governors_exception
ORDER BY is_governors_exception;
--  standard           3,932 · 3,808 scored · avg IMU 54.8
--  governors_exception  216 ·    70 scored · avg IMU 72.0  (above the 62.0 cutoff)

-- ============================================================================
-- (6) WHERE the active designations are, and how old each state's map is. Top
--     12 states by count, with each state's median designation date and the
--     share predating 2000. California and Texas lead by count, but North
--     Carolina and Georgia run a median date of 1978; Florida is the outlier
--     with a 2001 median and only 30.2% predating 2000.
-- ============================================================================
WITH d AS (
  SELECT * FROM public.hrsa_shortage_areas
  WHERE designation_kind = 'mua_mup' AND status = 'Designated' AND state IS NOT NULL
)
SELECT
  state,
  count(*)                                                          AS designations,
  percentile_disc(0.5) WITHIN GROUP (ORDER BY designation_date)     AS median_designation_date,
  round(100.0 * count(*) FILTER (WHERE designation_date < '2000-01-01') / count(*), 1) AS pct_pre_2000
FROM d
GROUP BY state
ORDER BY designations DESC
LIMIT 12;
--  CA 213  median 1994-08-01  69.5% pre-2000
--  TX 212  median 1984-07-03  65.1%
--  IL 176  median 1994-05-18  72.2%
--  GA 168  median 1978-11-01  82.7%
--  PA 156  median 1994-05-12  82.7%
--  OH 137  median 1994-05-12  79.6%
--  NY 132  median 1994-05-04  82.6%
--  FL 129  median 2001-09-26  30.2%   <- the outlier: a refreshed map
--  MO 123  median 1992-08-04  94.3%
--  MI 115  median 1994-05-27  67.8%
--  NC 113  median 1978-11-01  95.6%
--  KY 112  median 1990-11-23  67.0%

-- ============================================================================
-- (7) Structure: areas vs populations, and the exception split. 86.0% of the
--     active map is area-based (MUA), 14.0% population-based (MUP).
-- ============================================================================
SELECT
  designation_type,
  count(*)                                                          AS designations,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1)                AS pct_of_active
FROM public.hrsa_shortage_areas
WHERE designation_kind = 'mua_mup' AND status = 'Designated'
GROUP BY designation_type
ORDER BY designations DESC;
--  Medically Underserved Area .......................... 3,427  82.6%
--  Medically Underserved Population .....................  505  12.2%
--  Medically Underserved Area - Governor's Exception ....  140   3.4%
--  Medically Underserved Population - Governor's Exception   76   1.8%
--  (area-based = MUA + MUA-exception = 3,567, 86.0%; population-based = 581, 14.0%.)