-- Most of Medicare's posted revalidation deadlines are already past due, 2026
-- Fonteum reproducible query — https://fonteum.com/research/medicare-revalidation-backlog-2026
-- Data as of: 2026-06-15  ·  methodology: cms-revalidation/v1
-- Source datasets: CMS PECOS
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- Most of Medicare's POSTED revalidation deadlines are already past due.
-- Fully reproducible query.
--
-- Question: every Medicare provider and supplier must revalidate its
-- enrollment on a fixed cycle (5 years for most, 3 years for DMEPOS
-- suppliers). CMS publishes the Revalidation Due Date List — for each
-- enrollment, either an assigned due date or "TBD". Of the enrollments CMS
-- has actually assigned a due date, how many of those deadlines have already
-- passed, how old is the backlog, and what kind of enrollment does it
-- concentrate in? The lead figure: of the 261,878 enrollments carrying an
-- assigned due date, 217,968 — 83.2% — show a date already in the past as of
-- 2026-06-15. A revalidation due date is an administrative calendar control;
-- a date in the past is a TIMING signal, NOT a fraud, ineligibility, or
-- quality signal of any kind.
--
-- Source:
--   public.cms_revalidation_due_dates — CMS "Revalidation Due Date List",
--     published monthly via the CMS data catalog (data.cms.gov, Medicare
--     Provider/Supplier Enrollment). 2,900,588 enrollment records across
--     2,422,395 distinct NPIs; May 2026 release
--     (.../2026-05/.../revalidation_base.csv), ingested 2026-06-14. Public,
--     read-only. License: US-Government-Works (17 U.S.C. Sec. 105).
--     methodology_version = 'cms-revalidation/v1'.
--
-- Universe: this study reads the published file AS A WHOLE — every row is one
--   Medicare enrollment record. CMS posts an assigned due date only for
--   enrollments due within roughly the next six months; all others read
--   "TBD" (stored as NULL here). The dated rows are therefore the active
--   revalidation queue; the file is a point-in-time snapshot, not a trend.
--
-- As-of date: "overdue" = revalidation_due_date < DATE '2026-06-15', the
--   snapshot read date. Figures that depend on the as-of date (the total
--   overdue count) are stated as of that date; the prior-calendar-year
--   counts (2023-2025) are past for any read date in 2026 and are stable.
--   No individual provider is named, ranked, or scored anywhere below.

-- ============================================================================
-- (1) Universe reconciliation — the published file at a glance.
-- ============================================================================
SELECT
  count(*)                                                          AS enrollments,
  count(DISTINCT npi)                                               AS distinct_npi,
  count(*) FILTER (WHERE revalidation_due_date IS NULL)             AS tbd_no_date,
  count(*) FILTER (WHERE revalidation_due_date IS NOT NULL)         AS dated,
  count(DISTINCT enrollment_state)                                  AS states,
  count(DISTINCT enrollment_specialty)                             AS specialties,
  min(revalidation_due_date)                                        AS earliest_due,
  max(revalidation_due_date)                                        AS latest_due
FROM public.cms_revalidation_due_dates;
--  enrollments 2,900,588 · distinct_npi 2,422,395 · tbd_no_date 2,638,710 (91.0%)
--  dated 261,878 · states 56 · specialties 533
--  earliest_due 2023-07-31 · latest_due 2026-11-30

-- ============================================================================
-- (2) HEADLINE: of the enrollments CMS has assigned a due date, what share is
--     already past due as of the snapshot read date (2026-06-15)?
-- ============================================================================
SELECT
  count(*) FILTER (WHERE revalidation_due_date IS NOT NULL)         AS dated,
  count(*) FILTER (WHERE revalidation_due_date < DATE '2026-06-15') AS overdue,
  round(100.0 * count(*) FILTER (WHERE revalidation_due_date < DATE '2026-06-15')
    / count(*) FILTER (WHERE revalidation_due_date IS NOT NULL), 1) AS overdue_pct_of_dated,
  count(DISTINCT npi) FILTER (WHERE revalidation_due_date < DATE '2026-06-15')
                                                                    AS overdue_distinct_npi
FROM public.cms_revalidation_due_dates;
--  dated 261,878 · overdue 217,968 · overdue_pct_of_dated 83.2% · overdue_distinct_npi 193,508

-- ============================================================================
-- (3) The dated/overdue split by enrollment type. DME suppliers revalidate on
--     a 3-year cycle (vs 5 years for everyone else), so a far larger share of
--     DME enrollments sit inside the posted due-date window: 78.4% of DME
--     enrollments are dated, against 6.8% of Non-DME Part B.
-- ============================================================================
SELECT
  provider_type_text,
  count(*)                                                          AS enrollments,
  count(*) FILTER (WHERE revalidation_due_date IS NOT NULL)         AS dated,
  round(100.0 * count(*) FILTER (WHERE revalidation_due_date IS NOT NULL)
    / count(*), 1)                                                  AS dated_pct,
  count(*) FILTER (WHERE revalidation_due_date < DATE '2026-06-15') AS overdue
FROM public.cms_revalidation_due_dates
GROUP BY provider_type_text
ORDER BY enrollments DESC;
--  Non-DME Part B  2,757,567 · dated 187,722 ( 6.8%) · overdue 157,201
--  Part A             71,620 · dated  18,199 (25.4%) · overdue  15,776
--  DME                71,401 · dated  55,957 (78.4%) · overdue  44,991

-- ============================================================================
-- (4) HOW OLD the backlog is — overdue enrollments by the calendar year the
--     due date falls in. Every dated record from 2023, 2024 and 2025 is past
--     due (187,129 in total, the oldest 2023-07-31); the 2026 column splits
--     into the genuinely past-due (30,839) and the still-future window.
-- ============================================================================
SELECT
  extract(year FROM revalidation_due_date)::int                     AS due_year,
  count(*)                                                          AS dated,
  count(*) FILTER (WHERE revalidation_due_date < DATE '2026-06-15') AS overdue
FROM public.cms_revalidation_due_dates
WHERE revalidation_due_date IS NOT NULL
GROUP BY due_year
ORDER BY due_year;
--  2023 47,218 · overdue 47,218
--  2024 84,598 · overdue 84,598
--  2025 55,313 · overdue 55,313
--  2026 74,749 · overdue 30,839
--  (prior-calendar-year overdue, 2023-2025 = 187,129; total overdue = 217,968)

-- ============================================================================
-- (5) WHAT KIND of enrollment the backlog is — overdue cohort by enrollment
--     specialty, top 12. It is overwhelmingly ORGANIZATIONAL: 95.0% of overdue
--     enrollments carry an organization name (group practices, pharmacies,
--     agencies, suppliers) rather than an individual; Clinic/Group Practice
--     alone is 46.5% of the overdue cohort.
-- ============================================================================
SELECT
  enrollment_specialty,
  count(*) FILTER (WHERE revalidation_due_date < DATE '2026-06-15') AS overdue,
  round(100.0 * count(*) FILTER (WHERE revalidation_due_date < DATE '2026-06-15')
    / (SELECT count(*) FROM public.cms_revalidation_due_dates
       WHERE revalidation_due_date < DATE '2026-06-15'), 1)         AS pct_of_overdue
FROM public.cms_revalidation_due_dates
WHERE enrollment_specialty IS NOT NULL AND enrollment_specialty <> ''
GROUP BY enrollment_specialty
ORDER BY overdue DESC
LIMIT 12;
--  Clinic/Group Practice                  101,391  46.5%
--  Pharmacy                                31,152  14.3%
--  Mass Immunization (Roster Biller Only)  14,823   6.8%
--  Centralized Flu Biller                  10,803   5.0%
--  Home Health Agency                       4,271   2.0%
--  Physical/Occupational Therapy Group      3,986   1.8%
--  Ambulance Service Supplier               3,938   1.8%
--  Medical Supply Company                   3,286   1.5%
--  Federally Qualified Health Center        2,830   1.3%
--  Physician - Podiatry                     2,623   1.2%
--  Independent Clinical Laboratory          2,619   1.2%
--  Ambulatory Surgical Center               2,247   1.0%
--  (organization-named share of overdue = 207,177 of 217,968 = 95.0%)

-- ============================================================================
-- (6) WHERE the overdue enrollments are — top 10 states by overdue count, with
--     each state's own overdue share of its dated enrollments. The big states
--     lead on count (TX 27,670); several mid-size states show the highest
--     overdue shares (GA 93.3%, NC 92.1%, OH 91.6%, IL 91.5%).
-- ============================================================================
SELECT
  enrollment_state,
  count(*) FILTER (WHERE revalidation_due_date IS NOT NULL)         AS dated,
  count(*) FILTER (WHERE revalidation_due_date < DATE '2026-06-15') AS overdue,
  round(100.0 * count(*) FILTER (WHERE revalidation_due_date < DATE '2026-06-15')
    / NULLIF(count(*) FILTER (WHERE revalidation_due_date IS NOT NULL), 0), 1)
                                                                    AS overdue_pct_of_dated
FROM public.cms_revalidation_due_dates
WHERE enrollment_state IS NOT NULL
GROUP BY enrollment_state
ORDER BY overdue DESC
LIMIT 10;
--  TX 32,860 dated · 27,670 overdue · 84.2% · CA 20,974 · 16,150 · 77.0%
--  NY 13,986 · 10,948 · 78.3% · FL 13,166 · 10,752 · 81.7%
--  IL 10,981 · 10,044 · 91.5% · NC 10,209 · 9,402 · 92.1%
--  OH  9,400 ·  8,613 · 91.6% · GA  8,838 · 8,249 · 93.3%
--  MI  9,107 ·  7,108 · 78.0% · PA  8,840 · 6,970 · 78.8%
