NTSB investigations: Cessna A152

59 cases on aircraft the registry lists as Cessna A152. 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 ('2071836')) 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 ('2071836'))
  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 ('2071836'))
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 LAX81FUA01Accident · Minor injuriesClosedCessna A152 · Yorba Linda, CAOct 4, 1980 · N7306B · Substantial damage NTSB ATL80FA096Accident · No injuriesClosedCessna 152 · Bluefield, WVSep 13, 1980 · N4870A · Substantial damage NTSB CHI80DEP34Accident · No injuriesClosedCessna 152 · Watertown, WIMay 27, 1980 · N4809A · Substantial damage NTSB LAX80FVA12Accident · Serious injuriesClosedCessna AL52 · Mariposa, CAFeb 9, 1980 · N7314L · Substantial damage NTSB FTW80FA036Accident · 2 fatalClosedCessna A152 · Lomax, TXJan 15, 1980 · N7393L · Destroyed NTSB FTW80FA014Accident · 2 fatalClosedCessna 152 · Lillian, TXNov 17, 1979 · N7611B · Destroyed NTSB LAX80FUQ01Accident · Minor injuriesClosedCessna 152 · Ojai, CAOct 6, 1979 · N7603B · Destroyed NTSB FTW79DRG53Accident · No injuriesClosedCessna 152A · Junction, TXAug 24, 1979 · N4669A · Substantial damage NTSB IAD79FA013Accident · 1 fatalClosedCessna 152 · Spencer, WVNov 18, 1978 · N7359B · Destroyed