Type certificate data sheets

duckdb -csv -c "WITH fleet AS (
    SELECT a.tcds_number, count(*) AS aircraft
    FROM 'https://openairdocs.org/data/latest/faa_aircraft_registration.parquet' g JOIN 'https://openairdocs.org/data/latest/faa_aircraft_model.parquet' a ON a.code = g.aircraft_model_code
    WHERE a.tcds_number IS NOT NULL GROUP BY 1
    UNION ALL
    SELECT e.tcds_number, count(*)
    FROM 'https://openairdocs.org/data/latest/faa_aircraft_registration.parquet' g JOIN 'https://openairdocs.org/data/latest/faa_engine_model.parquet' e ON e.code = g.engine_model_code
    WHERE e.tcds_number IS NOT NULL GROUP BY 1
  ),
  models AS (
    SELECT tcds_number, count(*) AS models, string_agg(concat_ws(' ', model, unofficial_name), ' ') AS names
    FROM 'https://openairdocs.org/data/latest/faa_type_certificate_model.parquet' GROUP BY 1
  )
  SELECT t.tcds_number, t.holder, t.status, t.product_type, coalesce(m.models, 0) AS models,
    coalesce((SELECT sum(aircraft) FROM fleet f WHERE f.tcds_number = t.tcds_number), 0) AS aircraft, t.revision_date
  FROM 'https://openairdocs.org/data/latest/faa_type_certificate.parquet' t LEFT JOIN models m USING (tcds_number)
  ORDER BY aircraft DESC, t.status = 'Current' DESC, t.revision_date DESC NULLS LAST, t.tcds_number"

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.

WITH fleet AS (
  SELECT a.tcds_number, count(*) AS aircraft
  FROM 'https://openairdocs.org/data/latest/faa_aircraft_registration.parquet' g JOIN 'https://openairdocs.org/data/latest/faa_aircraft_model.parquet' a ON a.code = g.aircraft_model_code
  WHERE a.tcds_number IS NOT NULL GROUP BY 1
  UNION ALL
  SELECT e.tcds_number, count(*)
  FROM 'https://openairdocs.org/data/latest/faa_aircraft_registration.parquet' g JOIN 'https://openairdocs.org/data/latest/faa_engine_model.parquet' e ON e.code = g.engine_model_code
  WHERE e.tcds_number IS NOT NULL GROUP BY 1
),
models AS (
  SELECT tcds_number, count(*) AS models, string_agg(concat_ws(' ', model, unofficial_name), ' ') AS names
  FROM 'https://openairdocs.org/data/latest/faa_type_certificate_model.parquet' GROUP BY 1
)
SELECT t.tcds_number, t.holder, t.status, t.product_type, coalesce(m.models, 0) AS models,
  coalesce((SELECT sum(aircraft) FROM fleet f WHERE f.tcds_number = t.tcds_number), 0) AS aircraft, t.revision_date
FROM 'https://openairdocs.org/data/latest/faa_type_certificate.parquet' t LEFT JOIN models m USING (tcds_number)
ORDER BY aircraft DESC, t.status = 'Current' DESC, t.revision_date DESC NULLS LAST, t.tcds_number

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.