pip_distro_bits.bq

SELECT pip_version, distro_name, distro_version, libc_version, bits, download_count
FROM (
SELECT REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(LOWER(details.distro.name), " (gnu/)?linux", ""), "^red.?hat.*", "redhat"), "os x","macos") AS distro_name,
  details.distro.version as distro_version,
  details.distro.libc.version as libc_version,
  REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(file.filename, ".*manylinux.*_i686.whl$", "32"), ".*manylinux.*_x86_64.whl$", "64"), ".*manylinux.*", "") AS bits,
  details.installer.version as pip_version,
  COUNT(*) AS download_count,
FROM `the-psf.pypi.downloads*`
WHERE
  details.installer.version IS NOT NULL
  AND details.distro.name IS NOT NULL
  AND details.distro.libc.version IS NOT NULL
  AND details.installer.name = 'pip'
  AND REGEXP_CONTAINS(file.filename, '^numpy-.*-manylinux.*.whl$')
    -- Only query the last 30 days of history
  AND _TABLE_SUFFIX
    BETWEEN FORMAT_DATE(
      '%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY
  pip_version, distro_name, distro_version, libc_version, bits
)