-- The Name-Only Exclusion List — identifier coverage of the SAM.gov Exclusions registry.
-- Source: public.procurement_exclusion_links (the UEI/CAGE match layer, one row per
--         active exclusion, snapshot_date = '2026-06-18') joined to
--         public.sam_exclusions (the classification attribute) on record_key.
--
-- match_status (set by the ingest, deterministic):
--   held_name_only  := the published exclusion carries neither a UEI nor a CAGE code.
--   confirmed_uei   := the record carries a Unique Entity ID (UEI).
--   confirmed_cage  := the record carries a CAGE code but no UEI.
--
-- A "machine-matchable" record is confirmed_uei OR confirmed_cage: it can be matched
-- to a contractor record by a stable key. A held_name_only record can be matched only
-- by name. Re-running these queries against the 2026-06-18 snapshot reproduces every
-- headline figure exactly. Each query is annotated with its expected result.

-- (1) National headline — how the list identifies the parties on it.
select
  count(*)                                                  as total,            -- 324,126
  count(*) filter (where match_status = 'held_name_only')   as name_only,        -- 233,251
  count(*) filter (where match_status = 'confirmed_uei')     as uei,             --  90,859
  count(*) filter (where match_status = 'confirmed_cage')    as cage_only,       --      16
  count(*) filter (where match_status in ('confirmed_uei','confirmed_cage'))
                                                            as machine_matchable, --  90,875
  round(100.0 * count(*) filter (where match_status = 'held_name_only')
        / count(*), 1)                                       as name_only_pct,    --    72.0
  round(100.0 * count(*) filter (where match_status in ('confirmed_uei','confirmed_cage'))
        / count(*), 1)                                       as matchable_pct     --    28.0
from public.procurement_exclusion_links;

-- (2) Name-only share by classification (the attribute lives on sam_exclusions).
-- Individuals are almost never machine-matchable; firms almost always are.
select
  s.classification,
  count(*)                                                  as records,
  count(*) filter (where l.match_status = 'held_name_only')  as name_only,
  round(100.0 * count(*) filter (where l.match_status = 'held_name_only')
        / count(*), 1)                                       as name_only_pct
from public.procurement_exclusion_links l
join public.sam_exclusions s on s.record_key = l.exclusion_record_key
group by s.classification
order by records desc;
--  Individual                  258,536 / 231,422 / 89.5
--  Special Entity Designation   46,866 /     12 /  0.0
--  Firm                         16,243 /  1,162 /  7.2
--  Vessel                        2,481 /    655 / 26.4

-- (3) Name-only share by excluding agency (top excluders).
select
  coalesce(excluding_agency, '(unattributed)')              as code,
  count(*)                                                  as records,
  count(*) filter (where match_status = 'held_name_only')   as name_only,
  count(*) filter (where match_status = 'confirmed_uei')     as uei,
  count(*) filter (where match_status = 'confirmed_cage')    as cage,
  round(100.0 * count(*) filter (where match_status = 'held_name_only')
        / count(*), 1)                                       as name_only_pct
from public.procurement_exclusion_links
group by 1
order by records desc
limit 10;
--  HHS  137,396 / 131,226 /  6,168 / 2 / 95.5
--  OPM   80,948 /  68,327 / 12,621 / 0 / 84.4
--  OFAC  73,853 /  11,958 / 61,895 / 0 / 16.2
--  DOJ    6,490 /   4,368 /  2,122 / 0 / 67.3
--  HUD    4,464 /   3,492 /    972 / 0 / 78.2
--  EPA    4,375 /   2,879 /  1,490 / 6 / 65.8
