NTSB defining events

duckdb -csv -c "SELECT e.\"tier2Number\" AS event, max(e.\"eventTier2Name\") AS name,
    count(DISTINCT e.case_mkey) FILTER (WHERE e.\"isDefiningEvent\") AS defining,
    count(DISTINCT e.case_mkey) AS in_sequence,
    count(DISTINCT e.case_mkey) FILTER (WHERE e.\"isDefiningEvent\" AND c.\"highestInjuryLevel\" = 'Fatal') AS fatal_defining
  FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' e JOIN 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c ON c.\"mKey\" = e.case_mkey
  WHERE e.\"tier2Number\" GLOB '[0-9][0-9][0-9]' AND e.\"eventTier2Name\" IS NOT NULL AND e.case_mkey IN (SELECT case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' WHERE \"isDefiningEvent\")
  GROUP BY 1
  ORDER BY defining DESC, in_sequence DESC"

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

SELECT e."tier2Number" AS event, max(e."eventTier2Name") AS name,
  count(DISTINCT e.case_mkey) FILTER (WHERE e."isDefiningEvent") AS defining,
  count(DISTINCT e.case_mkey) AS in_sequence,
  count(DISTINCT e.case_mkey) FILTER (WHERE e."isDefiningEvent" AND c."highestInjuryLevel" = 'Fatal') AS fatal_defining
FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' e JOIN 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c ON c."mKey" = e.case_mkey
WHERE e."tier2Number" GLOB '[0-9][0-9][0-9]' AND e."eventTier2Name" IS NOT NULL AND e.case_mkey IN (SELECT case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' WHERE "isDefiningEvent")
GROUP BY 1
ORDER BY defining DESC, in_sequence 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.

duckdb -csv -c "WITH top AS (
    SELECT \"tier2Number\" AS event FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet'
    WHERE \"isDefiningEvent\" AND \"tier2Number\" GLOB '[0-9][0-9][0-9]'
    GROUP BY 1 ORDER BY count(DISTINCT case_mkey) DESC LIMIT 6
  )
  SELECT e.\"tier2Number\" AS event, max(e.\"eventTier2Name\") AS name, year(c.\"eventDate\") AS year, count(DISTINCT e.case_mkey) AS cases
  FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' e JOIN 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c ON c.\"mKey\" = e.case_mkey
  WHERE e.\"isDefiningEvent\" AND e.\"tier2Number\" IN (SELECT event FROM top)
  GROUP BY 1, 3
  ORDER BY 1, 3"

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

WITH top AS (
  SELECT "tier2Number" AS event FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet'
  WHERE "isDefiningEvent" AND "tier2Number" GLOB '[0-9][0-9][0-9]'
  GROUP BY 1 ORDER BY count(DISTINCT case_mkey) DESC LIMIT 6
)
SELECT e."tier2Number" AS event, max(e."eventTier2Name") AS name, year(c."eventDate") AS year, count(DISTINCT e.case_mkey) AS cases
FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' e JOIN 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c ON c."mKey" = e.case_mkey
WHERE e."isDefiningEvent" AND e."tier2Number" IN (SELECT event FROM top)
GROUP BY 1, 3
ORDER BY 1, 3

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.

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'))
  SELECT * FROM (
  SELECT 'Phase of flight' AS kind, n.name, count(DISTINCT e.case_mkey) AS cases
  FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' e JOIN phase_names n
    ON n.main = CASE WHEN TRY_CAST(e.\"tier1Number\" AS INTEGER) >= 990 THEN 990 ELSE TRY_CAST(e.\"tier1Number\" AS INTEGER) // 50 * 50 END
  WHERE e.\"isDefiningEvent\" AND e.\"tier1Number\" GLOB '[0-9][0-9]*'
  GROUP BY ALL
  UNION ALL
  SELECT 'CICTT category', e.\"cicttEventSOEGroup\", count(DISTINCT e.case_mkey)
  FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' e
  WHERE e.\"isDefiningEvent\" AND e.\"cicttEventSOEGroup\" <> ''
  GROUP BY ALL
  )
  QUALIFY row_number() OVER (PARTITION BY kind ORDER BY cases DESC) <= 15
  ORDER BY kind, cases DESC"

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

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'))
SELECT * FROM (
SELECT 'Phase of flight' AS kind, n.name, count(DISTINCT e.case_mkey) AS cases
FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' e JOIN phase_names n
  ON n.main = CASE WHEN TRY_CAST(e."tier1Number" AS INTEGER) >= 990 THEN 990 ELSE TRY_CAST(e."tier1Number" AS INTEGER) // 50 * 50 END
WHERE e."isDefiningEvent" AND e."tier1Number" GLOB '[0-9][0-9]*'
GROUP BY ALL
UNION ALL
SELECT 'CICTT category', e."cicttEventSOEGroup", count(DISTINCT e.case_mkey)
FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' e
WHERE e."isDefiningEvent" AND e."cicttEventSOEGroup" <> ''
GROUP BY ALL
)
QUALIFY row_number() OVER (PARTITION BY kind ORDER BY cases DESC) <= 15
ORDER BY kind, cases 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.