Final NTSB investigations

2,183 cases where the phase of flight was Uncontrolled descent.

Cases, newest first

Query with SQL
duckdb -csv -c "WITH phase_names(main, name) AS (VALUES (100, 'Prior to flight'), (150, 'Standing'), (200, 'Pushback/towing'), (250, 'Taxi'), (300, 'Takeoff'), (350, 'Initial climb'), (400, 'Enroute'), (450, 'Maneuvering'), (500, 'Approach'), (550, 'Landing'), (600, 'Emergency descent'), (650, 'Uncontrolled descent'), (700, 'Post-impact'), (750, 'After landing'), (800, 'Other'), (990, 'Unknown')),
    crosswalk(code, main, name) AS (VALUES (500, 150, 'Standing'), (501, 150, 'Standing - pre-flight'), (502, 150, 'Standing - starting engine(s)'), (503, 150, 'Standing - engine(s) operating'), (504, 150, 'Standing - engine(s) not operating'), (505, 150, 'Standing - idling rotors'), (510, 250, 'Taxi'), (511, 200, 'Taxi - pushback/tow'), (512, 250, 'Taxi - to takeoff'), (513, 250, 'Taxi - from landing'), (514, 250, 'Taxi - aerial'), (520, 300, 'Takeoff'), (521, 300, 'Takeoff - roll/run'), (522, 350, 'Takeoff - initial climb'), (523, 300, 'Takeoff - aborted'), (530, 400, 'Climb'), (531, 400, 'Climb - to cruise'), (540, 400, 'Cruise'), (541, 400, 'Cruise - normal'), (542, 400, 'Maneuvering - holding (IFR)'), (550, 400, 'Descent'), (551, 400, 'Descent - normal'), (552, 600, 'Descent - emergency'), (553, 650, 'Descent - uncontrolled'), (560, 500, 'Approach'), (561, 500, 'Approach - VFR pattern - downwind'), (562, 500, 'Approach - VFR pattern - base turn'), (563, 500, 'Approach - VFR pattern - base leg/base to final'), (564, 500, 'Approach - VFR pattern - final approach'), (565, 500, 'Go-around (VFR)'), (566, 500, 'Approach - IAF to FAF/outer marker (IFR)'), (567, 500, 'Approach - FAF/outer marker to threshold (IFR)'), (568, 500, 'Circling (IFR)'), (569, 500, 'Missed approach (IFR)'), (570, 550, 'Landing'), (571, 550, 'Landing - flare/touchdown'), (572, 550, 'Landing - roll'), (573, 550, 'Landing - aborted'), (574, 550, 'Emergency landing'), (575, 550, 'Emergency landing after takeoff'), (576, 600, 'Emergency descent/landing'), (580, 450, 'Maneuvering'), (581, 450, 'Maneuvering - aerial application'), (582, 450, 'Maneuvering - turn to reverse direction'), (583, 450, 'Maneuvering - turn to landing area (emergency)'), (590, 450, 'Hover'), (591, 450, 'Hover - in ground effect'), (592, 450, 'Hover - out of ground effect'), (600, 800, 'Other'), (610, 990, 'Unknown')),
    current_codes AS (
      SELECT e.case_mkey, TRY_CAST(e.\"tier1Number\" AS INTEGER) AS code,
        row_number() OVER (PARTITION BY e.case_mkey ORDER BY coalesce(e.\"isDefiningEvent\", false) DESC, e.position) AS n
      FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' e
      WHERE e.aircrafts_position = 1 AND e.\"tier1Number\" GLOB '[1-9][0-9]*'
        AND (e.\"isDefiningEvent\" OR e.case_mkey IN (SELECT \"mKey\" FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' WHERE \"isPre1982\"))
    ),
    phases AS (
      SELECT case_mkey, 'current' AS scheme, code, CASE WHEN code >= 990 THEN 990 ELSE code // 50 * 50 END AS main
      FROM current_codes WHERE n = 1
      UNION ALL
      SELECT o.case_mkey, 'older', o.\"phaseOfFlightCode\", w.main
      FROM 'https://openairdocs.org/data/latest/ntsb_occurrences.parquet' o JOIN crosswalk w ON w.code = o.\"phaseOfFlightCode\"
      WHERE o.aircrafts_position = 1 AND o.\"occurrenceNumber\" = 1 AND o.case_mkey NOT IN (SELECT case_mkey FROM current_codes)
    ),
    case_phases AS (
      SELECT p.*, c.\"eventDate\", c.\"highestInjuryLevel\" = 'Fatal' AS fatal, a.aircraft_model_code
      FROM phases p JOIN 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c ON c.\"mKey\" = p.case_mkey
      LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = p.case_mkey AND a.position = 1
      WHERE c.\"eventDate\" >= DATE '1962-01-01' AND p.main IN (SELECT main FROM phase_names)
    )
  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.\"completionStatus\" = 'Completed'
    AND c.\"mKey\" IN (SELECT case_mkey FROM case_phases WHERE main = 650)
  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 NYC81FA070Accident · 6 fatalClosedPiper PA-32 · Easton, PAJul 1, 1981 · N54383 · DestroyedNTSB ATL81FA087Accident · 4 fatalClosedPiper PA-32R · Vicksburg, MSJul 1, 1981 · N3570Q · DestroyedNTSB MKC81FA040Accident · 1 fatalClosedCessna 404 · Belton, KSJun 30, 1981 · N6810N · DestroyedNTSB FTW81FA105Accident · 2 fatalClosedSwearingen SA226T · Cameron, LAJun 10, 1981 · N555AM · DestroyedNTSB FTW81FA121Accident · 2 fatalClosedMooney M20J · Eagletown, OKJun 6, 1981 · N1039C · DestroyedNTSB LAX81FA091Accident · 1 fatalClosedPiper PA-30 · Moreno, CAMay 27, 1981 · N8457Y · DestroyedNTSB SEA81FA038Accident · 3 fatalClosedPiper PA-28 · Malad City, IDMay 25, 1981 · N2816F · DestroyedNTSB MKC81FA036Accident · 2 fatalClosedCessna 172 · Carbondale, KSMay 22, 1981 · N4509E · DestroyedNTSB ATL81FA074Accident · 2 fatalClosedBell 47G2A1 · Covington, KYMay 20, 1981 · N73263 · DestroyedNTSB DEN81FA035Accident · 4 fatalClosedCessna T210N · Castle Rock, COMay 18, 1981 · N5137Y · DestroyedNTSB FTW81FA089Accident · 2 fatalClosedCessna 337A · Evant, TXMay 15, 1981 · N6345F · DestroyedNTSB FTW81FA090Accident · 1 fatalClosedCessna 172A · Verda, LAMay 13, 1981 · N7232T · DestroyedNTSB NYC81FA047Accident · 4 fatalClosedCessna 177B · Forked River, NJMay 10, 1981 · N16083 · DestroyedNTSB DEN81FA032Accident · 3 fatalClosedCessna T210G · Worland, WYMay 7, 1981 · N6842R · DestroyedNTSB ATL81FA071Accident · 1 fatalClosedMooney M20J · Cades, SCMay 7, 1981 · N201YX · DestroyedNTSB CHI81DEV17Accident · No injuriesClosedBalloon WKS 7B · Normal, ILMay 2, 1981 · N15443 · Substantial damageNTSB FTW81FA083Accident · 3 fatalClosedPiper PA-31 · Hammond, TXMay 1, 1981 · N3544J · DestroyedNTSB LAX81FA080Accident · 1 fatalClosedCessna 170B · Steamboat, NVApr 30, 1981 · N2318D · DestroyedNTSB FTW81FA077Accident · 1 fatalClosedBeech A36 · Houston, TXApr 23, 1981 · N500CC · DestroyedNTSB FTW81FA074Accident · 4 fatalClosedBeech E35 · Fay, OKApr 18, 1981 · N3281C · DestroyedNTSB FTW81FA073Accident · 4 fatalClosedCessna 172N · Yoakum, TXApr 18, 1981 · N6525G · DestroyedNTSB NYC81FA040Accident · 1 fatalClosedPiper PA-28R · Hartford, NYApr 17, 1981 · N7435J · DestroyedNTSB MKC81FA028Accident · 1 fatalClosedPiper PA-28 · Dixon, IAApr 8, 1981 · N47970 · DestroyedNTSB MKC81FA027Accident · 2 fatalClosedPiper PA-28 · Creston, IAApr 8, 1981 · N2250W · DestroyedNTSB FTW81FQG22Accident · Serious injuriesClosedQuickie 01 · Abernathy, TXApr 7, 1981 · N34GD · DestroyedNTSB MKC81FA026Accident · 2 fatalClosedCessna 310R · Eldon, MOApr 5, 1981 · N1275G · DestroyedNTSB FTW81EA065Accident · 1 fatalClosedCessna 152 · Hempstead, TXApr 4, 1981 · N714VR · DestroyedNTSB DEN81FA023Accident · 2 fatalClosedMooney M20G · Ryegate, MTMar 21, 1981 · N6844N · DestroyedNTSB LAX81FA063Accident · 2 fatalClosedBeech B35 · Winkelman, AZMar 20, 1981 · N8587R · DestroyedNTSB CHI81FA034Accident · 2 fatalClosedCessna 172 · Lake Geneva, WIMar 10, 1981 · N733XU · DestroyedNTSB CHI81FA032Accident · 2 fatalClosedBeech P35 · Monarch Pass, COMar 6, 1981 · N8607M · DestroyedNTSB SEA81FA020Accident · 2 fatalClosedCessna 182P · Everett, WAMar 3, 1981 · N21002 · DestroyedNTSB ATL81FA051Accident · 1 fatalClosedPiper PA-30 · Autaugaville, ALMar 1, 1981 · N7443Y · DestroyedNTSB FTW81FA041Accident · 5 fatalClosedBeech A36TC · Nageezi, NMFeb 28, 1981 · N123AB · DestroyedNTSB FTW81FA040Accident · 2 fatalClosedBeech 58P · Ozona, TXFeb 28, 1981 · N411P · DestroyedNTSB NYC81FA029Accident · 4 fatalClosedMooney M20F · Montgomery Twp, NJFeb 22, 1981 · N1919T · DestroyedNTSB CHI81FA026Accident · 3 fatalClosedPiper PA-28 · Waupun, WIFeb 18, 1981 · N8422C · DestroyedNTSB LAX81FA072Accident · 4 fatalClosedPiper PA-28 · Truckee, CAFeb 13, 1981 · N9431W · DestroyedNTSB FTW81FA027Accident · 2 fatalClosedPiper PA-23 · Odessa, TXFeb 4, 1981 · N63900 · DestroyedNTSB CHI81FA022Accident · 1 fatalClosedCessna 150M · Timewell, ILJan 25, 1981 · N63090 · DestroyedNTSB LAX81FA037Accident · 4 fatalClosedBeech S35 · Casa Grande, AZJan 16, 1981 · N844B · DestroyedNTSB LAX81FA034Accident · 6 fatalClosedBeech A36 · Peach Springs, AZJan 12, 1981 · N66757 · DestroyedNTSB SEA81DYM15Accident · No injuriesClosedBell 206A · Mill City, ORJan 7, 1981 · N90099 · Substantial damageNTSB FTW81FA021Accident · 2 fatalClosedBellanca 17-30A · Iraan, TXJan 6, 1981 · N6606V · DestroyedNTSB MKC81FA012Accident · No injuriesClosedBeech F35 · Paris, TNJan 1, 1981 · N266S · Substantial damageNTSB CHI81FA014Accident · 2 fatalClosedBeech G35 · Harding, WIDec 30, 1980 · N190B · DestroyedNTSB NYC81DFA09Accident · No injuriesClosedPiper PA-28 · Palenville, NYDec 26, 1980 · N4308F · Substantial damageNTSB LAX81FA026Accident · 3 fatalClosedMooney M20K · Battle Mt, NVDec 24, 1980 · N231NX · DestroyedNTSB DEN81FA011Accident · 2 fatalClosedCessna 310K · Boulder, CODec 23, 1980 · N7058L · Substantial damageNTSB SEA81FA012Accident · 2 fatalClosedPiper PA-34 · Stevenson, WADec 21, 1980 · N114PH · Destroyed