-- Software Is Now the Most-Recalled Vehicle Component.
-- Source: NHTSA ODI Recalls flat files loaded to public.nhtsa_recalls.
-- Snapshot checked: 2026-06-24.
-- Unit of analysis: recall records are make x model x model-year x campaign rows,
-- not unique recall campaigns and not affected-vehicle counts.
-- Aggregate-only: no VINs, complaints, individuals, or defect narratives are selected.

WITH current_recalls AS (
  SELECT
    record_id,
    campaign_number,
    make,
    model,
    model_year,
    component_name,
    manufacturer
  FROM public.nhtsa_recalls
  WHERE valid_to IS NULL
)
SELECT count(*)::int AS total_recall_records
FROM current_recalls;
-- Expected: 242041

WITH current_recalls AS (
  SELECT component_name
  FROM public.nhtsa_recalls
  WHERE valid_to IS NULL
)
SELECT
  component_name AS recalled_component,
  count(*)::int AS recall_records
FROM current_recalls
WHERE component_name IS NOT NULL
  AND btrim(component_name) <> ''
GROUP BY component_name
ORDER BY recall_records DESC, recalled_component
LIMIT 5;
-- Expected:
-- ELECTRICAL SYSTEM:SOFTWARE                         20508
-- TIRES:MARKINGS                                    19460
-- COMMUNICATION:AUTO CRASH NOTIFICATION             16688
-- EQUIPMENT                                          9993
-- FUEL SYSTEM, GASOLINE:DELIVERY:FUEL PUMP           8404

WITH current_recalls AS (
  SELECT manufacturer
  FROM public.nhtsa_recalls
  WHERE valid_to IS NULL
)
SELECT
  manufacturer,
  count(*)::int AS recall_records
FROM current_recalls
WHERE manufacturer IS NOT NULL
  AND btrim(manufacturer) <> ''
GROUP BY manufacturer
ORDER BY recall_records DESC, manufacturer
LIMIT 5;
-- Expected:
-- Mercedes-Benz USA          44209
-- Prinx Chengshan Tire       19321
-- Toyota Motor Eng. & Mfg    14123
-- Ford                       12587
-- Honda                      12091
