Medically Underserved Areas: 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.
-- ============================================================================
-- audit-claim: universe
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.
-- ============================================================================
-- audit-claim: decade-vector
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

-- Direct summary outputs used by the narrative and founding-cohort claims.
-- audit-claim: age-and-update-summary
SELECT
  count(*) FILTER (WHERE designation_date < DATE '2000-01-01') AS pre_2000,
  round(100.0 * count(*) FILTER (WHERE designation_date < DATE '2000-01-01') / count(*), 1) AS pre_2000_pct,
  percentile_disc(0.5) WITHIN GROUP (ORDER BY designation_date) AS median_designation_date,
  count(*) FILTER (WHERE designation_date >= DATE '2016-01-01') AS designated_since_2016,
  round(100.0 * count(*) FILTER (WHERE designation_date >= DATE '2016-01-01') / count(*), 1) AS designated_since_2016_pct,
  count(*) FILTER (WHERE last_update_date = designation_date) AS never_updated,
  round(100.0 * count(*) FILTER (WHERE last_update_date = designation_date) / count(*), 1) AS never_updated_pct,
  percentile_disc(0.5) WITHIN GROUP (ORDER BY last_update_date) AS median_last_update_date,
  count(*) FILTER (
    WHERE designation_date = DATE '1978-11-01'
      AND last_update_date = designation_date
  ) AS founding_cohort_never_updated
FROM public.hrsa_shortage_areas
WHERE designation_kind = 'mua_mup' AND status = 'Designated';
-- pre_2000 2,991 (72.1%) · median designation 1994-05-11
-- since 2016 324 (7.8%) · unchanged 3,030 (73.0%)
-- median last update 1994-06-03 · founding-cohort unchanged 718

-- ============================================================================
-- (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.
-- ============================================================================
-- audit-claim: top-designation-dates
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,311
--     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. Of 270
--     active designations with no published IMU, 124 are standard rows and 146
--     are among the 216 Governor's Exceptions; those populations overlap.
-- ============================================================================
-- audit-claim: imu-band-vector
WITH active AS (
  SELECT * FROM public.hrsa_shortage_areas
  WHERE designation_kind = 'mua_mup' AND status = 'Designated'
)
SELECT
  count(*) FILTER (
    WHERE designation_type NOT LIKE '%Exception%' AND score > 0
  )                                                                  AS scored_standard,
  round(avg(score) FILTER (
    WHERE designation_type NOT LIKE '%Exception%' AND score > 0
  ), 1)                                                              AS mean_imu,
  count(*) FILTER (
    WHERE designation_type NOT LIKE '%Exception%' AND score > 0 AND score <= 40
  )                                                                  AS band_le_40,
  count(*) FILTER (
    WHERE designation_type NOT LIKE '%Exception%' AND score > 40 AND score <= 50
  )                                                                  AS band_40_50,
  count(*) FILTER (
    WHERE designation_type NOT LIKE '%Exception%' AND score > 50 AND score <= 55
  )                                                                  AS band_50_55,
  count(*) FILTER (
    WHERE designation_type NOT LIKE '%Exception%' AND score > 55 AND score <= 62
  )                                                                  AS band_55_62,
  count(*) FILTER (
    WHERE designation_type NOT LIKE '%Exception%' AND score > 62
  )                                                                  AS band_gt_62,
  round(100.0 * count(*) FILTER (
    WHERE designation_type NOT LIKE '%Exception%' AND score > 55 AND score <= 62
  ) / count(*) FILTER (
    WHERE designation_type NOT LIKE '%Exception%' AND score > 0
  ), 1)                                                              AS band_55_62_pct,
  count(*) FILTER (
    WHERE designation_type LIKE '%Exception%' AND score > 0
  )                                                                  AS scored_exception,
  count(*) FILTER (
    WHERE designation_type LIKE '%Exception%'
  )                                                                  AS exception_designations,
  count(*) FILTER (
    WHERE designation_type NOT LIKE '%Exception%' AND coalesce(score, 0) <= 0
  )                                                                  AS standard_missing_imu,
  count(*) FILTER (
    WHERE designation_type LIKE '%Exception%' AND coalesce(score, 0) <= 0
  )                                                                  AS exception_missing_imu,
  count(*) FILTER (WHERE coalesce(score, 0) <= 0)                    AS missing_imu,
  count(*)                                                           AS active_designations
FROM active;
--  a. IMU <= 40 (deepest need) ......  166
--  b. IMU 40-50 .....................  546
--  c. IMU 50-55 .....................  777
--  d. IMU 55-62 (just under cutoff) . 2,311   <- 60.7% of scored standard designations
--  e. IMU > 62 (above cutoff) .......    8
--  (mean IMU across these 3,808 scored standard designations = 54.8; range 18.1-74.7.)
--  reconciliation: 4,148 active - 216 exceptions - 124 scoreless standard = 3,808
--  missing IMU: 124 standard + 146 exception = 270; the 146 sit within the 216 exceptions.

-- ============================================================================
-- (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.
-- ============================================================================
-- audit-claim: exception-vector
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,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1)                AS exception_pct_of_active
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.
-- ============================================================================
-- audit-claim: state-vector
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%

-- Direct North Carolina values used outside the public top-eight table.
-- audit-claim: north-carolina-age
SELECT
  count(*)                                                           AS north_carolina_designations,
  percentile_disc(0.5) WITHIN GROUP (ORDER BY designation_date)      AS median_designation_date,
  round(100.0 * count(*) FILTER (WHERE designation_date < DATE '2000-01-01') / count(*), 1)
                                                                     AS pre_2000_pct
FROM public.hrsa_shortage_areas
WHERE designation_kind = 'mua_mup'
  AND status = 'Designated'
  AND state = 'NC';
-- North Carolina 113 · median 1978-11-01 · 95.6% pre-2000

-- ============================================================================
-- (7) Structure: areas vs populations, and the exception split. 86.0% of the
--     active map is area-based (MUA), 14.0% population-based (MUP).
-- ============================================================================
-- audit-claim: designation-type-vector
SELECT
  count(*) FILTER (WHERE designation_type LIKE 'Medically Underserved Area%')
                                                                     AS area_based,
  round(100.0 * count(*) FILTER (
    WHERE designation_type LIKE 'Medically Underserved Area%'
  ) / count(*), 1)                                                   AS area_based_pct,
  count(*) FILTER (WHERE designation_type LIKE 'Medically Underserved Population%')
                                                                     AS population_based,
  round(100.0 * count(*) FILTER (
    WHERE designation_type LIKE 'Medically Underserved Population%'
  ) / count(*), 1)                                                   AS population_based_pct
FROM public.hrsa_shortage_areas
WHERE designation_kind = 'mua_mup' AND status = 'Designated';
-- area-based 3,567 (86.0%) · population-based 581 (14.0%)