NTSB investigations: Amateur-built UNKNOWN ENG, 2002, CA

4 cases on aircraft whose registry engine model is Amateur-built UNKNOWN ENG. 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.

Cases per year

19642026
NTSB investigations: Amateur-built UNKNOWN ENG, 2002, CA per year, 1964–2026: 222 cases
19642
19652
19665
19674
19681
19691
19702
19711
19721
19731
19742
19754
19766
19773
19784
19792
19804
19821
19832
19843
19852
19862
19877
19883
19892
19903
19923
19933
19944
19954
19962
19985
19992
200010
20014
20024
20037
20043
20053
200610
20076
20088
20095
20108
20112
20125
20131
20144
20153
20168
20176
20184
20198
20201
20213
20223
20233
20242
20254
20264

Latest cases

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 year(c.\"eventDate\") = 2002
    AND c.\"eventStateOrRegion\" = 'CA' AND c.\"eventCountry\" = 'USA'
    AND c.\"mKey\" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.engine_model_code IN ('99999'))
  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.