get-pip-statistics.sql

SELECT
  DATE(timestamp) AS date,
  details.system.name AS os,
  file.project AS project,
  REGEXP_REPLACE(file.version, "\\.[0123456789]{1,}$", "") AS version,
  COUNT(*) AS count
FROM `the-psf.pypi.downloads*`
WHERE
  _TABLE_SUFFIX BETWEEN '20160101' AND '20210224'
  AND (file.project = "rootpy" OR
       file.project = "uproot" OR
       file.project = "uproot3" OR
       file.project = "uproot4" OR
       file.project = "awkward" OR
       file.project = "awkward0" OR
       file.project = "awkward1" OR
       file.project = "root-numpy" OR
       file.project = "root_numpy" OR
       file.project = "root-pandas" OR
       file.project = "root_pandas" OR
       file.project = "mplhep" OR
       file.project = "boost-histogram" OR
       file.project = "boost_histogram" OR
       file.project = "physt" OR
       file.project = "histoprint" OR
       file.project = "iminuit" OR
       file.project = "probfit" OR
       file.project = "zfit" OR
       file.project = "lmfit" OR
       file.project = "goofit" OR
       file.project = "hepstats" OR
       file.project = "pyhf" OR
       file.project = "reana" OR
       file.project = "heppyfwk" OR
       file.project = "coffea" OR
       file.project = "cabinetry" OR
       file.project = "hepaccelerate" OR
       file.project = "hepunits" OR
       file.project = "fast-carpenter" OR
       file.project = "fast_carpenter" OR
       file.project = "particle" OR
       file.project = "pyjet" OR
       file.project = "astropy" OR
       file.project = "rucio" OR
       file.project = "uncertainties")
  AND details.installer.name = "pip"
GROUP BY project, version, date, os
ORDER BY project, version, date, os