NTSB investigations: Cessna 421

60 cases on aircraft the registry lists as Cessna 421. Aircraft are matched to the registry by N-number and event date, checked against the reported serial number, make and model, so older matches can be wrong.

Defining event

Finding

Phase of flight

Aircraft model

Cases, newest first

Query with SQL

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."mKey" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('2076010')) ORDER BY c."eventDate" DESC, c."ntsbNumber" DESC

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.\"mKey\" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('2076010'))
  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.

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."mKey" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('2076010'))
ORDER BY c."eventDate" DESC, c."ntsbNumber" DESC

Run it at the DuckDB prompt, or in any tool that reads Parquet over HTTPS. More ways to query the release are on Use the data.

NTSB NYC73AN084Accident · 3 fatalClosedCessna 421 · Cheektowaga, NYDec 16, 1972 · N121X · Destroyed NTSB SEA73FTI13Accident · No injuriesClosedCessna 421 · Kalispell, MTOct 7, 1972 · N424WP · Destroyed NTSB MIA73AM014Accident · 5 fatalClosedCessna 421 · Cleveland, TNAug 7, 1972 · N123QF · Destroyed NTSB OAK73DVW04Accident · No injuriesClosedCessna 421 · Bishop, CAJul 12, 1972 · N4048L · Substantial damage NTSB CHI71FBJ54Accident · Serious injuriesClosedCessna 421 · Reed City, MIJan 29, 1971 · N4555L · Destroyed NTSB NYC71AN059Accident · 2 fatalClosedCessna 421 · Bath, PANov 11, 1970 · N3155K · Destroyed NTSB NYC70FHD08Accident · No injuriesClosedCessna 421 · Blue Bell, PAJan 29, 1970 · N3155K · Substantial damage NTSB CHI69F0419Accident · No injuriesClosedCessna 421 · Elkhart, INDec 26, 1968 · N3160K · Substantial damage NTSB OAK68D0224Accident · Minor injuriesClosedCessna 421 · Salinas, CANov 29, 1967 · N4005L · Substantial damage NTSB CHI68F0457Accident · Minor injuriesClosedCessna 421 · Findlay, OHNov 3, 1967 · N4019L · Destroyed