Fatal NTSB investigations, PR

180,658 investigations by the National Transportation Safety Board since 1962: 174,636 accidents, the rest incidents and other occurrences. Each has NTSB’s narrative and, once the investigation is done, its probable cause; since 2008, its findings and sequence of events.

Cases, newest first

Query with SQL
duckdb -csv -c "SELECT c.\"ntsbNumber\", c.\"eventDate\", c.\"eventType\", c.\"eventCity\", c.\"eventStateOrRegion\", a.n_number, a.\"aircraftMake\", a.\"aircraftModel\", c.\"highestInjuryLevel\", c.\"totalFatal\", c.\"completionStatus\", c.report_url
  FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = c.\"mKey\" AND a.position = 1
  WHERE c.\"eventStateOrRegion\" = 'PR' AND c.\"eventCountry\" = 'USA'
    AND c.\"highestInjuryLevel\" = 'Fatal'
  ORDER BY c.\"eventDate\" DESC, c.\"ntsbNumber\" DESC"

Prints the whole list as CSV. It reads the release’s Parquet files over HTTPS, so there’s nothing to download first. Install DuckDB to run it.

NTSB MIA71AM108Accident · 1 fatalClosedCessna 172E · Villalba, PRJun 6, 1971 · N3710S · DestroyedNTSB MIA71AM089Accident · 4 fatalClosedPiper PA-28 · Yabucoa, PRApr 18, 1971 · N4853L · DestroyedNTSB MIA71AM070Accident · 3 fatalClosedPiper PA-28 · Aguadilla, PRFeb 7, 1971 · N3680K · DestroyedNTSB MIA71AM038Accident · 1 fatalClosedBeech C-45H · Carolina, PROct 28, 1970 · N349V · Minor damageNTSB MIA71AM024Accident · 1 fatalClosedBrittnorman BN-2A · Orocovis, PRSep 9, 1970 · N855JA · DestroyedNTSB MIA70A0030Accident · 1 fatalClosedBeech C-45H · Loiza Aldea, PROct 11, 1969 · N9957Z · DestroyedNTSB MIA69A0090Accident · 1 fatalClosedCessna 150G · Vega Baja, PRJun 17, 1969 · N3159X · DestroyedNTSB MIA69A0051Accident · 19 fatalClosedDehavilland DH1142 · San Juan, PRMar 5, 1969 · N563PR · DestroyedNTSB MIA68A0090Accident · 1 fatalClosedAero Comdr 680 · San Juan, PRApr 20, 1968 · N408EE · DestroyedNTSB MIA68A0072Accident · 4 fatalClosedBeech 35-33 · Gurabo, PRFeb 26, 1968 · N9577Y · DestroyedNTSB MIA68A0034Accident · 2 fatalClosedCessna 172 · Isle Culebra, PROct 8, 1967 · N8372U · DestroyedNTSB MIA68A0002Accident · 4 fatalClosedPiper PA-28 · Mayaguez, PRJul 2, 1967 · N5100W · DestroyedNTSB MIA66A0089Accident · 3 fatalClosedCessna 172F · Palmer, PRFeb 15, 1966 · N7848U · DestroyedNTSB MIA66A0013Accident · 1 fatalClosedCessna 170B · Corozal, PRJul 20, 1965 · N2860C · DestroyedNTSB MIA65A0096Accident · 3 fatalClosedPiper PA-22 · Caguas, PRMar 27, 1965 · N9518D · Destroyed