Registry models it may concern · 652

duckdb -csv -c "-- One row per registry model code; the page joins codes that share a name
  SELECT 'aircraft' AS kind, m.code, m.manufacturer, m.model, count(g.n_number) AS aircraft
  FROM 'https://openairdocs.org/data/latest/faa_aircraft_model.parquet' m LEFT JOIN 'https://openairdocs.org/data/latest/faa_aircraft_registration.parquet' g ON g.aircraft_model_code = m.code
  WHERE m.code IN (SELECT aircraft_model_code FROM 'https://openairdocs.org/data/latest/faa_aircraft_model_saib.parquet' WHERE saib_id = '267EE7BA9F05B58786257D9A00659E8B')
  GROUP BY ALL
  UNION ALL
  SELECT 'engine' AS kind, m.code, m.manufacturer, m.model, count(g.n_number) AS aircraft
  FROM 'https://openairdocs.org/data/latest/faa_engine_model.parquet' m LEFT JOIN 'https://openairdocs.org/data/latest/faa_aircraft_registration.parquet' g ON g.engine_model_code = m.code
  WHERE m.code IN (SELECT engine_model_code FROM 'https://openairdocs.org/data/latest/faa_engine_model_saib.parquet' WHERE saib_id = '267EE7BA9F05B58786257D9A00659E8B')
  GROUP BY ALL
  ORDER BY aircraft DESC, code"

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.

-- One row per registry model code; the page joins codes that share a name
SELECT 'aircraft' AS kind, m.code, m.manufacturer, m.model, count(g.n_number) AS aircraft
FROM 'https://openairdocs.org/data/latest/faa_aircraft_model.parquet' m LEFT JOIN 'https://openairdocs.org/data/latest/faa_aircraft_registration.parquet' g ON g.aircraft_model_code = m.code
WHERE m.code IN (SELECT aircraft_model_code FROM 'https://openairdocs.org/data/latest/faa_aircraft_model_saib.parquet' WHERE saib_id = '267EE7BA9F05B58786257D9A00659E8B')
GROUP BY ALL
UNION ALL
SELECT 'engine' AS kind, m.code, m.manufacturer, m.model, count(g.n_number) AS aircraft
FROM 'https://openairdocs.org/data/latest/faa_engine_model.parquet' m LEFT JOIN 'https://openairdocs.org/data/latest/faa_aircraft_registration.parquet' g ON g.engine_model_code = m.code
WHERE m.code IN (SELECT engine_model_code FROM 'https://openairdocs.org/data/latest/faa_engine_model_saib.parquet' WHERE saib_id = '267EE7BA9F05B58786257D9A00659E8B')
GROUP BY ALL
ORDER BY aircraft DESC, code

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.