-- The Most-Recalled Vehicle Brands — aggregate counts from the NHTSA recall record.
-- Warehouse: fonteum-platform. Source table:
--   public.nhtsa_recalls  -- NHTSA FLAT_RCL flat file, snapshot 2026-06-24
--
-- GRAIN: the flat file has one row per make x model x component within a recall
-- campaign, so the "recall" unit is the distinct campaign_number. The
-- potentially_affected count is constant across a campaign's rows (asserted: 0
-- campaigns have a varying value), so it is read once per campaign with max().
-- Every figure below is a GROUP BY count; no individual is named.

-- (0) Dataset-level totals (campaign grain, affected deduped to one value/campaign).
with camp as (
  select campaign_number, max(potentially_affected) as affected
  from public.nhtsa_recalls group by campaign_number
)
select
  count(*)                                                   as total_campaigns,        -- 15,035
  sum(affected)                                              as total_affected_units,   -- 691,537,329
  round(avg(affected))                                       as mean_affected,          -- 45,995
  percentile_cont(0.5) within group (order by affected)      as median_affected,        --    475
  (select count(distinct make) from public.nhtsa_recalls
     where make is not null and btrim(make) <> '')           as distinct_makes,         --  1,766
  (select count(distinct component_name) from public.nhtsa_recalls
     where component_name is not null and btrim(component_name) <> '') as distinct_components, -- 624
  (select min(report_received_date) from public.nhtsa_recalls)        as report_start,  -- 2010-01-01
  (select max(report_received_date) from public.nhtsa_recalls)        as report_end     -- 2026-06-19
from camp;

-- (1) Most-recalled brands — distinct campaigns + affected units per make. A
--     campaign that spans more than one make is counted for each make it lists.
with mk as (
  select make, campaign_number, max(potentially_affected) as affected
  from public.nhtsa_recalls
  where make is not null and btrim(make) <> ''
  group by make, campaign_number
)
select make,
       count(*)       as recall_campaigns,   -- FORD 847, MERCEDES-BENZ 512, CHEVROLET 438 ...
       sum(affected)  as affected_units      -- FORD 92,368,181 ...
from mk group by make order by recall_campaigns desc limit 25;

-- (1b) The same brands ranked by potentially-affected units (a different order;
--      "TAKATA" appears as a make — the airbag-inflator supplier's own recalls).
with mk as (
  select make, campaign_number, max(potentially_affected) as affected
  from public.nhtsa_recalls
  where make is not null and btrim(make) <> ''
  group by make, campaign_number
)
select make, count(*) as recall_campaigns, sum(affected) as affected_units
from mk group by make order by affected_units desc limit 10;
--  FORD 92,368,181 / TAKATA 87,467,531 / CHEVROLET 70,254,735 / TOYOTA 61,472,351 ...

-- (2) By component system — distinct campaigns citing each component.
with cm as (
  select component_name, campaign_number, max(potentially_affected) as affected
  from public.nhtsa_recalls
  where component_name is not null and btrim(component_name) <> ''
  group by component_name, campaign_number
)
select component_name, count(*) as recall_campaigns, sum(affected) as affected_units
from cm group by component_name order by recall_campaigns desc limit 20;
--  EQUIPMENT 875 / ... AIR BAGS:FRONTAL:PASSENGER SIDE:INFLATOR MODULE 206 (92,675,054 affected) ...

-- (3) By report year (campaign grain). 2026 is partial (cut at the snapshot date).
with camp as (
  select campaign_number, max(potentially_affected) as affected,
         max(extract(year from report_received_date)) as yr
  from public.nhtsa_recalls
  where report_received_date is not null
  group by campaign_number
)
select yr::int as year, count(*) as recall_campaigns, sum(affected) as affected_units
from camp group by yr order by yr;
--  2010 722 / ... 2021 1,094 (peak) / ... 2026 428 (partial) ...

-- (4) Concentration — the largest brands' share of all make-campaign appearances.
with mk as (
  select make, count(distinct campaign_number) as campaigns
  from public.nhtsa_recalls
  where make is not null and btrim(make) <> ''
  group by make
), ranked as (
  select make, campaigns, row_number() over (order by campaigns desc) as rk,
         sum(campaigns) over () as total_pairs
  from mk
)
select
  max(total_pairs)                                                          as total_make_campaign_appearances, -- 17,596
  sum(campaigns) filter (where rk = 1)                                      as top1,      --   847
  sum(campaigns) filter (where rk <= 5)                                     as top5,      -- 2,560
  sum(campaigns) filter (where rk <= 10)                                    as top10,     -- 3,883
  sum(campaigns) filter (where rk <= 25)                                    as top25,     -- 6,757
  round(100.0*sum(campaigns) filter (where rk = 1)/max(total_pairs),1)      as top1_pct,  --   4.8
  round(100.0*sum(campaigns) filter (where rk <= 5)/max(total_pairs),1)     as top5_pct,  --  14.5
  round(100.0*sum(campaigns) filter (where rk <= 10)/max(total_pairs),1)    as top10_pct, --  22.1
  round(100.0*sum(campaigns) filter (where rk <= 25)/max(total_pairs),1)    as top25_pct  --  38.4
from ranked;

-- (4b) The top brand's share of the 15,035 DISTINCT campaigns, and the share of
--      campaigns that span more than one brand.
with per_campaign as (
  select campaign_number, count(distinct make) as makes
  from public.nhtsa_recalls
  where make is not null and btrim(make) <> ''
  group by campaign_number
)
select
  round(100.0*847/count(*),1)                                          as top1_share_of_distinct_pct, -- 5.6
  round(100.0*count(*) filter (where makes > 1)/count(*),1)            as multi_brand_campaign_pct    -- 12.8
from per_campaign;
