recent_pip_versions.bq

select pip_version, distro_name, distro_version, (download_count / total * 100) as percent
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.installer.version as pip_version,
  COUNT(*) AS download_count,
  SUM(COUNT(*)) OVER () AS total
FROM `the-psf.pypi.downloads*`
WHERE 
  details.installer.version IS NOT NULL
  AND details.distro.name IS NOT NULL
  AND details.distro.name != 'os x'
    -- 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
ORDER BY
  pip_version, download_count DESC
)
WHERE download_count / total * 100 > 0.5