Affected TCDS · 57

duckdb -csv -c "SELECT p.tcds_number, any_value(t.holder) AS holder, any_value(t.status) AS status, list(DISTINCT coalesce(p.tcds_model, p.model)) AS models
  FROM 'https://openairdocs.org/data/latest/faa_directive_product.parquet' p LEFT JOIN 'https://openairdocs.org/data/latest/faa_type_certificate.parquet' t USING (tcds_number)
  WHERE p.ad_id = 'FAA:2023-09-09' AND p.tcds_number IS NOT NULL
  GROUP BY p.tcds_number
  ORDER BY len(models) DESC, p.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.

SELECT p.tcds_number, any_value(t.holder) AS holder, any_value(t.status) AS status, list(DISTINCT coalesce(p.tcds_model, p.model)) AS models
FROM 'https://openairdocs.org/data/latest/faa_directive_product.parquet' p LEFT JOIN 'https://openairdocs.org/data/latest/faa_type_certificate.parquet' t USING (tcds_number)
WHERE p.ad_id = 'FAA:2023-09-09' AND p.tcds_number IS NOT NULL
GROUP BY p.tcds_number
ORDER BY len(models) DESC, p.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.