# Queries: The New Medicaid Workforce

The PostgreSQL queries behind every figure on https://getprovyx.com/research/medicaid-workforce-npi-growth/.

```sql
-- Provyx research: "The New Medicaid Workforce" (2026-11-09)
-- Source: CMS NPPES full data dissemination file npidata_pfile_20050523-20261011.csv
--   (October 11, 2026; 9,852,180 records), loaded into PostgreSQL tables nppes_ref.providers,
--   nppes_ref.taxonomy_assignments and nppes_ref.taxonomy_codes (refresh_id 5 = this file; refresh_id 4 = the September 2026 file).
-- License: CC BY 4.0, Provyx (https://getprovyx.com/research/medicaid-workforce-npi-growth/).
\pset footer off

-- Q0. File identity
select id, source_filename, provider_count, lifecycle from nppes_ref.refreshes where id in (4,5) order by id;

-- Q1. Taxonomy codes (searched by keyword; display_name confirmed)
select code, grouping, classification, specialization, display_name from nppes_ref.taxonomy_codes
where code in ('374J00000X','175T00000X','172V00000X','373H00000X','372600000X','251C00000X') order by code;

-- Q2. Data shape checks: every active record has exactly one primary taxonomy;
--     deactivated records carry no taxonomy and no enumeration date.
select n_primary, count(*) from (select npi, sum(is_primary::int) n_primary from nppes_ref.taxonomy_assignments where refresh_id=5 group by npi) x group by 1;
select count(*) deactivated, count(enumeration_date) with_enum_date, count(entity_type) with_entity_type
from nppes_ref.providers where refresh_id=5 and deactivation_date is not null and reactivation_date is null;
select (select count(*) from nppes_ref.providers p where refresh_id=5 and deactivation_date is not null and reactivation_date is null
        and exists (select 1 from nppes_ref.taxonomy_assignments t where t.refresh_id=5 and t.npi=p.npi)) deactivated_with_taxonomy;
select min(enumeration_date), max(enumeration_date) from nppes_ref.providers where refresh_id=5;

-- Base: active records with their primary taxonomy and enumeration year
create temp table prof(code text primary key, profession text);
insert into prof values ('374J00000X','Doula'),('175T00000X','Peer Specialist'),('172V00000X','Community Health Worker'),
 ('373H00000X','Day Training/Habilitation Specialist'),('372600000X','Adult Companion');
create temp table pp as
select p.npi, p.entity_type, coalesce(nullif(p.practice_state,''),'(blank)') state,
       extract(year from p.enumeration_date)::int yr, t.taxonomy_code
from nppes_ref.providers p
join nppes_ref.taxonomy_assignments t on t.refresh_id=5 and t.npi=p.npi and t.is_primary
where p.refresh_id=5;

-- Q3. New NPIs per year by profession (primary taxonomy), with all new NPIs as denominator -> *_by_year.csv
\echo CSV_BY_YEAR
\copy (with d as (select yr, count(*) all_new from pp group by yr) select x.yr as year, (x.yr=2026) as partial_year, pr.profession, pr.code as taxonomy_code, count(*) as new_npis, sum((x.entity_type=1)::int) as type1_individual, sum((x.entity_type=2)::int) as type2_organization, d.all_new as all_new_npis, round(100.0*count(*)/d.all_new,3) as share_of_all_new_pct from pp x join prof pr on pr.code=x.taxonomy_code join d on d.yr=x.yr where x.yr between 2015 and 2026 group by x.yr, pr.profession, pr.code, d.all_new order by pr.profession, x.yr) to stdout csv header

-- Q4. New NPIs per year by profession and practice state -> *_by_state.csv (pivoted locally)
\echo CSV_BY_STATE_LONG
\copy (select pr.profession, x.state, x.yr, count(*) n from pp x join prof pr on pr.code=x.taxonomy_code where x.yr between 2015 and 2026 group by 1,2,3 order by 1,2,3) to stdout csv header

-- Q5. Same five codes in ANY taxonomy slot, 2025 (context: secondary listings)
\echo ANY_SLOT_2025
select t.taxonomy_code, count(distinct p.npi) from nppes_ref.providers p
join nppes_ref.taxonomy_assignments t on t.refresh_id=5 and t.npi=p.npi
where p.refresh_id=5 and p.enumeration_date between '2025-01-01' and '2025-12-31'
  and t.taxonomy_code in (select code from prof) group by 1 order by 1;

-- Q6. September file (refresh 4) 2025 counts, to test whether the file vintage explains the handoff numbers
\echo REFRESH4_2025
select t.taxonomy_code, count(*) from nppes_ref.providers p
join nppes_ref.taxonomy_assignments t on t.refresh_id=4 and t.npi=p.npi and t.is_primary
where p.refresh_id=4 and p.enumeration_date between '2025-01-01' and '2025-12-31'
  and t.taxonomy_code in (select code from prof) group by 1 order by 1;

-- Q7. Nebraska context: all new NPIs per year, top primary taxonomies 2025, monthly adult companion / day hab
\echo NE_BY_YEAR
select yr, count(*) from pp where state='NE' and yr>=2020 group by 1 order by 1;
\echo NE_TOP_2025
select x.taxonomy_code, c.display_name, count(*) n from pp x left join nppes_ref.taxonomy_codes c on c.code=x.taxonomy_code
where x.state='NE' and x.yr=2025 group by 1,2 order by 3 desc limit 12;
\echo NE_MONTHLY
select to_char(p.enumeration_date,'YYYY-MM') m, sum((t.taxonomy_code='372600000X')::int) adult_companion, sum((t.taxonomy_code='373H00000X')::int) day_hab
from nppes_ref.providers p join nppes_ref.taxonomy_assignments t on t.refresh_id=5 and t.npi=p.npi and t.is_primary
where p.refresh_id=5 and p.practice_state='NE' and p.enumeration_date between '2024-10-01' and '2025-12-31'
  and t.taxonomy_code in ('372600000X','373H00000X') group by 1 order by 1;
\echo HHA_2025_NE_SHARE
select count(*) filter (where state='NE') ne, count(*) us from pp where yr=2025 and taxonomy_code='374U00000X';

-- Q8. Deactivations by deactivation year (cannot be attributed to a profession or enumeration year: the file blanks both)
\echo DEACT_BY_YEAR
select extract(year from deactivation_date)::int yr, count(*) from nppes_ref.providers
where refresh_id=5 and deactivation_date is not null and reactivation_date is null and deactivation_date>='2015-01-01' group by 1 order by 1;

-- Q9. Day Training agency code (organizations, excluded from the study; context only)
\echo DAY_TRAINING_AGENCY
select yr, count(*) from pp where taxonomy_code='251C00000X' and yr between 2015 and 2026 group by 1 order by 1;
```
