-- The FDA Drug Recall List, Analyzed: 2026 Trends by Severity
-- Fonteum reproducible query — https://fonteum.com/research/fda-drug-device-recalls-2026
-- Data as of: 2026-06-14  ·  methodology: fda-recalls/v1
-- Source datasets: FDA Enforcement Reports
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- FDA drug & device recall severity — fully reproducible query.
--
-- Question: when the FDA pulls a drug or a medical device, how serious is it
-- usually? We measure, over every recall enforcement record on file, the share
-- that carries each FDA classification (Class I = most serious, a reasonable
-- probability of serious injury or death; Class II = mid-severity; Class III =
-- least serious), the device-vs-drug split, the per-product class mix, the
-- voluntary-vs-mandated share, and recall volume by year.
--
-- Source:
--   public.fda_enforcement_reports — FDA recall enforcement reports via
--     openFDA (drug + device enforcement endpoints). One row per recall
--     enforcement record. 56,777 rows; public, read-only.
--     License: US-Government-Works (17 U.S.C. §105).
--
-- Universe: every row. No suppression, no sampling. A single recall event can
--   span many models/lots, and one event can generate several records, so the
--   unit is the recall record, not a product, a lot, or a patient.
--
-- Snapshot: figures are point-in-time to the 2026-06-14 openFDA snapshot
--   (max report_date 2026-06-03). The file is updated continuously and recalls
--   are reclassified/terminated over time, so the bands shift between snapshots.

-- ============================================================================
-- Audit observation: 2026-07-14. Source snapshot remains 2026-06-14.
-- audit-claim: recall-severity-vector
--     Class I share (9.2%) is the lead figure.
-- ============================================================================
SELECT classification,
       count(*)                                            AS recalls,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1)  AS share_pct
FROM public.fda_enforcement_reports
GROUP BY classification
ORDER BY recalls DESC;
--  Class II    48,820   86.0
--  Class I      5,237    9.2   <- most serious; ~1 in 11 recalls
--  Class III    2,720    4.8

-- ============================================================================
-- audit-claim: recall-product-vector
-- ============================================================================
SELECT product_kind,
       count(*)                                                          AS recalls,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1)                AS share_pct,
       count(*) FILTER (WHERE classification = 'Class I')                AS class1,
       round(100.0 * count(*) FILTER (WHERE classification = 'Class I')
             / count(*), 1)                                              AS class1_pct,
       count(*) FILTER (WHERE classification = 'Class II')               AS class2,
       round(100.0 * count(*) FILTER (WHERE classification = 'Class II')
             / count(*), 1)                                              AS class2_pct,
       count(*) FILTER (WHERE classification = 'Class III')              AS class3,
       round(100.0 * count(*) FILTER (WHERE classification = 'Class III')
             / count(*), 1)                                              AS class3_pct
FROM public.fda_enforcement_reports
GROUP BY product_kind
ORDER BY recalls DESC;
--  device  39,095  68.9  | I 3,510 (9.0)  II 34,560 (88.4)  III 1,025 (2.6)
--  drug    17,682  31.1  | I 1,727 (9.8)  II 14,260 (80.6)  III 1,695 (9.6)

-- ============================================================================
-- audit-claim: recall-initiation-summary
-- ============================================================================
SELECT
  count(*)                                                            AS recalls,
  count(*) FILTER (WHERE voluntary_mandated ILIKE 'Voluntary%')       AS voluntary,
  round(100.0 * count(*) FILTER (WHERE voluntary_mandated ILIKE 'Voluntary%')
        / count(*), 1)                                                AS voluntary_pct,
  count(*) FILTER (WHERE voluntary_mandated ILIKE '%mandated%')       AS mandated,
  round(100.0 * count(*) FILTER (WHERE voluntary_mandated ILIKE '%mandated%')
        / count(*), 1)                                                AS mandated_pct,
  count(DISTINCT firm_name_normalized)                               AS distinct_firms
FROM public.fda_enforcement_reports;
--  recalls 56,777 · voluntary 56,430 (99.4%) · mandated 302 (0.5%)
--  distinct_firms 4,204   (45 records carry neither label cleanly)

-- ============================================================================
-- audit-claim: recall-year-vector
--     2026 excluded as a partial year; pre-2012 is < 0.8% of the file.
-- ============================================================================
SELECT date_part('year', recall_initiation_date)::int             AS yr,
       count(*)                                                   AS recalls,
       count(*) FILTER (WHERE classification = 'Class I')         AS class1,
       count(*) FILTER (WHERE product_kind = 'drug')              AS drugs,
       count(*) FILTER (WHERE product_kind = 'device')            AS devices
FROM public.fda_enforcement_reports
WHERE recall_initiation_date >= '2015-01-01'
  AND recall_initiation_date <  '2026-01-01'
GROUP BY 1
ORDER BY 1;
--  2015 4,640 / I 202 / drug 1,733 / device 2,907
--  2016 4,228 / I 175 / drug 1,199 / device 3,029
--  2017 4,454 / I 203 / drug 1,141 / device 3,313
--  2018 4,694 / I 652 / drug 1,681 / device 3,013
--  2019 4,798 / I 418 / drug 1,788 / device 3,010
--  2020 3,532 / I 287 / drug   930 / device 2,602
--  2021 3,412 / I 295 / drug 1,152 / device 2,260
--  2022 3,667 / I 275 / drug 1,391 / device 2,276
--  2023 3,723 / I 670 / drug 1,122 / device 2,601
--  2024 3,832 / I 384 / drug   557 / device 3,275
--  2025 3,608 / I 422 / drug   778 / device 2,830

-- ============================================================================
-- audit-claim: recall-reason-vector
--     reason field is free text, so a recall can match more than one bucket).
--     Directional signal only, not a partition.
-- ============================================================================
WITH r AS (
  SELECT product_kind, lower(reason_for_recall) AS rsn
  FROM public.fda_enforcement_reports
)
SELECT product_kind,
  count(*) FILTER (WHERE rsn ~ 'contaminat|microbial|steril|bacteria|fungal|mold|particulate|foreign') AS contamination_sterility,
  count(*) FILTER (WHERE rsn ~ 'label|mislabel|incorrect.*label|packag')                                AS labeling_packaging,
  count(*) FILTER (WHERE rsn ~ 'specification|out of spec|potency|subpotent|superpotent|assay|impurit|dissolution|stability|degrad') AS specs_potency,
  count(*) FILTER (WHERE rsn ~ 'software|malfunction|defective|failure|fail to|may fail|component')      AS device_defect,
  count(*) FILTER (WHERE rsn ~ 'cgmp|gmp|manufactur|process')                                            AS manufacturing,
  count(*)                                                                                               AS total
FROM r
GROUP BY product_kind
ORDER BY total DESC;
--  device | contam 7,839 · label 7,461 · specs 2,494 · defect 6,300 · mfg 5,229 · total 39,095
--  drug   | contam 9,226 · label 2,569 · specs 3,613 · defect   517 · mfg 5,134 · total 17,682

-- ============================================================================
-- audit-claim: recall-universe
-- ============================================================================
SELECT
  count(*)                                                        AS total_rows,
  count(*) FILTER (WHERE product_kind = 'drug')                   AS drug_rows,
  count(*) FILTER (WHERE product_kind = 'device')                 AS device_rows,
  count(DISTINCT firm_name_normalized)                            AS distinct_firms,
  count(*) FILTER (WHERE recall_initiation_date >= '2012-01-01')  AS y2012plus,
  min(recall_initiation_date)                                     AS earliest,
  max(recall_initiation_date)                                     AS latest
FROM public.fda_enforcement_reports;
--  total_rows 56,777 · drug 17,682 · device 39,095 · firms 4,204
--  y2012plus 56,321 (99.2%) · earliest 1930-12-11 (single outlier) · latest 2026-05-18
