-- The UN Consolidated Sanctions List, Profiled — composition + cadence.
-- Source: public.un_sanctions_listings — the United Nations Security Council
--         Consolidated Sanctions List, parsed direct from scsanctions.un.org
--         (snapshot_date = '2026-06-20', the list's own @dateGenerated).
--
-- Every query is filtered to the current bitemporal slice (valid_to IS NULL) and
-- uses listed_on (the UN "listed on" designation date) for the year/era cuts.
-- party_type is 'person' or 'entity'. Re-running these against the 2026-06-20
-- snapshot reproduces every headline figure exactly; each query is annotated with
-- its expected result. No party is named — these are GROUP BY counts only.

-- (1) National headline — totals, the person/entity split, and the era cuts.
select
  count(*)                                                      as total,        -- 1,002
  count(*) filter (where party_type = 'person')                as persons,       --   730
  count(*) filter (where party_type = 'entity')                as entities,      --   272
  count(distinct committee)                                    as committees,    --    14
  count(*) filter (where listed_on < '2010-01-01')             as era_2000s,     --   459
  count(*) filter (where listed_on >= '2010-01-01'
                     and listed_on < '2020-01-01')             as era_2010s,     --   471
  count(*) filter (where listed_on >= '2020-01-01')            as era_2020s,     --    72
  count(*) filter (where listed_on >= '2022-01-01')            as since_2022,    --    46
  round(100.0 * count(*) filter (where listed_on < '2020-01-01')
        / count(*), 1)                                         as pre_2020_pct,  --  92.8
  round(100.0 * count(*) filter (where listed_on >= '2022-01-01')
        / count(*), 1)                                         as since_2022_pct -- 4.6
from public.un_sanctions_listings
where valid_to is null;

-- (2) Alias coverage + median age of a designation.
select
  count(*) filter (where array_length(aliases, 1) > 0)         as with_alias,    --   712
  count(*) filter (where aliases is null
                     or array_length(aliases, 1) is null)      as no_alias,      --   290
  sum(coalesce(array_length(aliases, 1), 0))                   as total_aliases, -- 2,730
  max(coalesce(array_length(aliases, 1), 0))                   as max_aliases,   --    27
  round((percentile_cont(0.5) within group (order by (snapshot_date - listed_on))
         / 365.25)::numeric, 1)                                as median_age_yrs --  16.0
from public.un_sanctions_listings
where valid_to is null;

-- (3) By sanctions committee, with dormancy (years since the last addition).
select
  committee,
  committee_slug,
  count(*)                                                     as total,
  count(*) filter (where party_type = 'person')                as persons,
  count(*) filter (where party_type = 'entity')                as entities,
  max(listed_on)                                               as last_addition,
  round(((max(snapshot_date) - max(listed_on)) / 365.25)::numeric, 1)
                                                               as years_since_last
from public.un_sanctions_listings
where valid_to is null
group by committee, committee_slug
order by total desc;
--  Al-Qaida 336 / Iraq last 2006 (20.1y) / Guinea-Bissau 2012 (13.9y) / Iran 2012 (13.5y)
--  6 of 14 committees have added nobody since before 2019.

-- (4) Designations by year (the cadence series; powers the CSV download).
select
  extract(year from listed_on)::int                            as year,
  count(*)                                                     as total,
  count(*) filter (where party_type = 'person')                as persons,
  count(*) filter (where party_type = 'entity')                as entities
from public.un_sanctions_listings
where valid_to is null
group by year
order by year;
--  Peak single year: 2001 = 141 (the post-9/11 Al-Qaida tranche, 14.1% of the list).
