-- FMCSA Carrier Safety 2026 — re-derivation SQL
-- Run against: fonteum-platform (public schema)
-- Source release / snapshot as-of: 2026-06-25
-- Methodology: fmcsa-carrier-safety/v1
-- License: U.S. Government Works (public domain).
-- All figures are aggregate counts only; no individual is named. Carrier names
-- appear only as public FMCSA records next to FMCSA's own published rating.
--
-- The table is bitemporal: every aggregate filters valid_to IS NULL (the current
-- row per record). row_kind discriminates carrier-census vs crash vs inspection.
-- Against ~11.15M current rows, a full-partition aggregate may exceed a short
-- statement timeout; each query below partitions over the bigint primary key id
-- (carrier rows occupy the low id range, then crash, then inspection) and the
-- partial sums reconcile to the totals shown.

-- 1. Current-row counts by record kind
SELECT row_kind, COUNT(*) AS rows
FROM public.fmcsa_carrier_safety_records
WHERE valid_to IS NULL
GROUP BY row_kind ORDER BY rows DESC;
-- expected: carrier 4,457,540 | crash 3,590,465 | inspection 3,102,000

-- 2. Carrier registration status (the operating-status proxy)
-- status_code lives in the census details payload: A = Active, I = Inactive.
SELECT
  COALESCE(details->>'status_code','—') AS status_code,
  COUNT(*) AS carriers,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
FROM public.fmcsa_carrier_safety_records
WHERE valid_to IS NULL AND row_kind = 'carrier'
GROUP BY 1 ORDER BY carriers DESC;
-- expected: I 2,254,345 (50.6%) | A 2,202,214 (49.4%) | other 981

-- 3. Safety-rating distribution (rated subset) + still-Active count
SELECT
  safety_rating,
  COUNT(*) AS carriers,
  COUNT(*) FILTER (WHERE details->>'status_code' = 'A') AS active_carriers
FROM public.fmcsa_carrier_safety_records
WHERE valid_to IS NULL AND row_kind = 'carrier' AND safety_rating IS NOT NULL
GROUP BY safety_rating ORDER BY carriers DESC;
-- expected: S 41,551 (41,173 active) | C 11,225 (11,020 active) | U 759 (744 active)
-- rated total 53,535 of 4,457,540 carriers (1.2%); 4,404,005 carry no rating.

-- 4. Crash severity profile
SELECT
  COUNT(*) AS crashes,
  COALESCE(SUM(fatalities),0) AS fatalities,
  COALESCE(SUM(injuries),0)   AS injuries,
  COUNT(*) FILTER (WHERE fatalities > 0) AS fatal_crashes,
  COUNT(*) FILTER (WHERE injuries  > 0) AS injury_crashes,
  COUNT(*) FILTER (WHERE (details->>'tow_away')::boolean IS TRUE) AS tow_away_crashes,
  COUNT(*) FILTER (WHERE (details->>'federal_recordable')::boolean IS TRUE) AS federal_recordable
FROM public.fmcsa_carrier_safety_records
WHERE valid_to IS NULL AND row_kind = 'crash';
-- expected: 3,590,465 crashes | 138,535 fatalities | 2,121,852 injuries
--           117,113 fatal (3.3%) | 1,450,319 injury (40.4%) | 3,305,201 tow-away (92.1%)

-- 5. Roadside-inspection out-of-service profile
-- driver_oos_total / vehicle_oos_total live in the inspection details payload.
SELECT
  COUNT(*) AS inspections,
  COUNT(*) FILTER (WHERE COALESCE(out_of_service_count,0) > 0)        AS inspections_with_oos,
  COUNT(*) FILTER (WHERE (details->>'driver_oos_total')::int  > 0)    AS driver_oos_inspections,
  COUNT(*) FILTER (WHERE (details->>'vehicle_oos_total')::int > 0)    AS vehicle_oos_inspections,
  COALESCE(SUM((details->>'viol_total')::int),0)                      AS total_violations,
  COALESCE(SUM((details->>'driver_oos_total')::int),0)               AS driver_oos_violations,
  COALESCE(SUM((details->>'vehicle_oos_total')::int),0)              AS vehicle_oos_violations,
  COALESCE(SUM(out_of_service_count),0)                              AS total_oos_violations
FROM public.fmcsa_carrier_safety_records
WHERE valid_to IS NULL AND row_kind = 'inspection';
-- expected: 3,102,000 inspections | 580,764 with an OOS order (18.7%)
--           170,793 driver-OOS (5.5%) | 459,395 vehicle-OOS (14.8%)
--           5,080,994 total violations | 877,652 total OOS violations

-- 6. Carriers by state (registered + active + unsafe-rated), top US states
SELECT
  state,
  COUNT(*) AS carriers,
  COUNT(*) FILTER (WHERE details->>'status_code' = 'A')               AS active,
  COUNT(*) FILTER (WHERE safety_rating = 'C')                         AS conditional,
  COUNT(*) FILTER (WHERE safety_rating = 'U')                         AS unsatisfactory
FROM public.fmcsa_carrier_safety_records
WHERE valid_to IS NULL AND row_kind = 'carrier'
GROUP BY state ORDER BY carriers DESC;
-- expected (top 3): CA 497,891 (324,803 active) | TX 371,284 (189,878) | FL 284,693 (153,733)
-- expected (most Unsatisfactory): NC 84 | KY 81 | IN 59 | MI 54 | OH 54

-- 7. Largest still-Active carriers FMCSA rated Unsatisfactory (the top-N table)
SELECT
  carrier_name, state,
  details->>'status_code'        AS status_code,
  (details->>'power_units')::int AS power_units,
  (details->>'total_drivers')::int AS total_drivers,
  safety_rating_date
FROM public.fmcsa_carrier_safety_records
WHERE valid_to IS NULL AND row_kind = 'carrier'
  AND safety_rating = 'U' AND details->>'status_code' = 'A'
ORDER BY (details->>'power_units')::int DESC NULLS LAST
LIMIT 15;
-- Names are public FMCSA motor-carrier records shown with FMCSA's own rating.
