duckdb-top-x86_64-recipes.sql

CREATE OR REPLACE TABLE stats AS
SELECT * FROM read_csv('./packages.tsv',
  delim = '\t',
  header = true,
  columns = {
    'package': 'VARCHAR',
    'total': 'INTEGER'
  }) 
WHERE package NOT LIKE 'bioconductor-%'
  AND package NOT LIKE 'perl-%'
;

CREATE OR REPLACE TABLE aarch64 AS
SELECT * FROM read_csv('./aarch64-status.tsv',
  delim = '\t',
  header = true,
  columns = {
    'package': 'VARCHAR',
    'has_aarch64': 'INTEGER',
    'noarch': 'INTEGER'
  }) 
;

SELECT stats.*
FROM stats JOIN aarch64 ON (stats.package = aarch64.package)
WHERE aarch64.has_aarch64 = 0 AND aarch64.noarch = 0
ORDER BY stats.total DESC
LIMIT 40
;