-- The Most-Recalled Product Brands — aggregate counts from the CPSC recall record.
-- Warehouse: fonteum-platform. Source table:
--   public.cpsc_recalls  -- CPSC saferproducts.gov Recall feed, snapshot 2026-06-24
--
-- GRAIN: one row per CPSC recall (the distinct recall_number); the affected-unit
-- total is one value per recall and is summed once. The firm field
-- (manufacturer_names) and product_types / hazard_names are text[] arrays and are
-- unnested. CPSC annotates each firm with a location (", of <City>, <State>"); the
-- ", of ..." suffix is stripped so the same firm is not split into fragments — the
-- ONLY transformation applied; names are otherwise as published. Every figure below
-- is a GROUP BY count; no individual is named.
--
-- BITEMPORAL: public.cpsc_recalls retains superseded rows; every query filters to the
-- current row (valid_to is null) so counts stay reproducible as amendments accumulate.

-- (0) Dataset-level totals (recall grain; units summed once per recall).
select
  count(*)                                                       as total_recalls,             -- 9,862
  sum(total_units_affected)                                      as total_units_affected,       -- 651,831,573
  round(avg(total_units_affected) filter (where total_units_affected is not null)) as mean_units, --  80,523
  percentile_cont(0.5) within group (order by total_units_affected)
    filter (where total_units_affected is not null)             as median_units,               --   7,000
  count(*) filter (where total_units_affected is not null)       as recalls_with_units,         --   8,095
  (select count(distinct recall_number) from public.cpsc_recalls
     where valid_to is null and array_length(manufacturer_names,1) >= 1) as recalls_naming_firm, --   5,419
  (select count(distinct btrim(split_part(m,', of ',1)))
     from public.cpsc_recalls, unnest(manufacturer_names) as m
     where valid_to is null and btrim(m) <> '')                 as distinct_brands,            --   5,646
  (select count(distinct btrim(p))
     from public.cpsc_recalls, unnest(product_types) as p
     where valid_to is null and btrim(p) <> '')                 as distinct_product_categories, --    645
  (select count(distinct recall_number) from public.cpsc_recalls c, unnest(c.product_types) p
     where c.valid_to is null and btrim(p) <> '')               as recalls_with_product_category, -- 6,844
  (select count(distinct btrim(h))
     from public.cpsc_recalls, unnest(hazard_names) as h
     where valid_to is null and btrim(h) <> '' and char_length(btrim(h)) <= 40 and btrim(h) !~ '\.') as distinct_coded_hazards, -- 23
  (select count(distinct recall_number) from public.cpsc_recalls c
     where c.valid_to is null and exists (select 1 from unnest(c.hazard_names) h
       where btrim(h) <> '' and char_length(btrim(h)) <= 40 and btrim(h) !~ '\.')) as recalls_with_coded_hazard, -- 1,588
  min(recall_date)                                              as recall_period_start,        -- 1973-06-08
  max(recall_date)                                              as recall_period_end           -- 2026-06-18
from public.cpsc_recalls where valid_to is null;

-- (1) Most-recalled brands — distinct recalls + affected units per firm. The
--     CPSC ", of <location>" suffix is stripped to consolidate same-firm fragments.
--     A recall naming more than one firm is counted for each firm it lists.
with mk as (
  select btrim(split_part(m, ', of ', 1)) as brand, recall_number,
         max(total_units_affected) as units
  from public.cpsc_recalls, unnest(manufacturer_names) as m
  where valid_to is null and btrim(m) <> ''
  group by btrim(split_part(m, ', of ', 1)), recall_number
)
select brand, count(*) as recalls, sum(units) as units_affected
from mk group by brand order by recalls desc, units_affected desc nulls last limit 25;
--  Sears 81 / Polaris Industries Inc. 79 (987,103) / Kmart 56 / Toys R Us 55 / Wal-Mart 46 ...

-- (2) Coded hazard categories — distinct recalls per CPSC controlled-vocabulary
--     label. Free-text hazard sentences (long, sentence-cased) are excluded by the
--     length/no-period filter, leaving the 1,588 recalls with a coded category.
with hz as (
  select btrim(h) as hazard, recall_number
  from public.cpsc_recalls, unnest(hazard_names) as h
  where valid_to is null and btrim(h) <> '' and char_length(btrim(h)) <= 40 and btrim(h) !~ '\.'
  group by btrim(h), recall_number
)
select hazard, count(*) as recalls
from hz group by hazard order by recalls desc limit 18;
--  Choking 386 / Fire & Fire-Related Burn 324 / Laceration 181 / Electrocution/Electric Shock 178 ...

-- (3) Product categories — distinct recalls per category (a legacy field present
--     on 6,844 recalls, absent after 2019).
with pt as (
  select btrim(p) as product_category, recall_number, max(total_units_affected) as units
  from public.cpsc_recalls, unnest(product_types) as p
  where valid_to is null and btrim(p) <> ''
  group by btrim(p), recall_number
)
select product_category, count(*) as recalls, sum(units) as units_affected
from pt group by product_category order by recalls desc limit 20;
--  Clothing (Children) 259 / Bicycles & Accessories 194 / Toy Animals Stuffed/Not Stuffed 155 ...

-- (4) By recall year (recall grain). The affected-unit field is unreported before
--     1999; 2026 is partial (cut at the snapshot date).
select extract(year from recall_date)::int as year,
       count(*) as recalls, sum(total_units_affected) as units_affected
from public.cpsc_recalls where valid_to is null
group by year order by year;
--  ... 2007 452 (busiest year) ... 2016 330 (133,411,768 units — a 118M-lb propane recall) ... 2026 291 (partial)

-- (5) Concentration — the largest brands' share of all brand-recall appearances.
with mk as (
  select btrim(split_part(m, ', of ', 1)) as brand,
         count(distinct recall_number) as recalls
  from public.cpsc_recalls, unnest(manufacturer_names) as m
  where valid_to is null and btrim(m) <> ''
  group by btrim(split_part(m, ', of ', 1))
), ranked as (
  select brand, recalls, row_number() over (order by recalls desc) as rk,
         sum(recalls) over () as total_pairs
  from mk
)
select
  max(total_pairs)                                                       as total_brand_recall_appearances, -- 8,116
  sum(recalls) filter (where rk = 1)                                     as top1,      --  81
  sum(recalls) filter (where rk <= 5)                                    as top5,      -- 317
  sum(recalls) filter (where rk <= 10)                                   as top10,     -- 465
  sum(recalls) filter (where rk <= 25)                                   as top25,     -- 685
  round(100.0*sum(recalls) filter (where rk = 1)/max(total_pairs),1)     as top1_pct,  -- 1.0
  round(100.0*sum(recalls) filter (where rk <= 5)/max(total_pairs),1)    as top5_pct,  -- 3.9
  round(100.0*sum(recalls) filter (where rk <= 10)/max(total_pairs),1)   as top10_pct, -- 5.7
  round(100.0*sum(recalls) filter (where rk <= 25)/max(total_pairs),1)   as top25_pct  -- 8.4
from ranked;

-- (5b) The top brand's share of the 5,419 recalls that name a firm, and the share
--      of named-firm recalls that list more than one firm.
with per_recall as (
  select recall_number, count(distinct btrim(split_part(m, ', of ', 1))) as firms
  from public.cpsc_recalls, unnest(manufacturer_names) as m
  where valid_to is null and btrim(m) <> ''
  group by recall_number
)
select
  round(100.0*81/count(*),1)                                          as top1_share_of_named_pct, -- 1.5
  round(100.0*count(*) filter (where firms > 1)/count(*),1)           as multi_firm_recall_pct    -- 18.6
from per_recall;
