Query pypi package download stats

bigquery.sql

SELECT details.installer.name, COUNT(*)
FROM
  TABLE_DATE_RANGE(
    [the-psf:pypi.downloads],
    TIMESTAMP("20160601"),
    TIMESTAMP("20160630")
  )
WHERE file.project='django-material'
group by details.installer.name

https://bigquery.cloud.google.com/dataset/the-psf:pypi

More gists - https://gist.github.com/alex/4f100a9592b05e9b4d63

SELECT count(*), country_code, file.filename, details.installer.version, details.python,
         details.distro.name, details.distro.version, details.distro.id, details.distro.libc.version,
         details.system.release, details.cpu, details.openssl_version, tls_protocol, tls_cipher
FROM
  TABLE_DATE_RANGE(
    [the-psf:pypi.downloads],
    TIMESTAMP("20170101"),
    TIMESTAMP("20170531")
  )
WHERE file.project='django-fsm'
and details.installer.name='pip'
group by country_code, file.filename, details.installer.version, details.python,
         details.distro.name, details.distro.version, details.distro.id, details.distro.libc.version,
         details.system.release, details.cpu, details.openssl_version, tls_protocol, tls_cipher
order by 1 desc

SELECT file.project, COUNT(*)
FROM
  TABLE_DATE_RANGE(
    [the-psf:pypi.downloads],
    TIMESTAMP("20170101"),
    TIMESTAMP("20170619")
  )
WHERE file.project like 'django%'
  and details.installer.name = 'pip'
group by file.project
order by 2 desc

SELECT DATE(timestamp), COUNT(*)
FROM
  TABLE_DATE_RANGE(
    [the-psf:pypi.downloads],
    TIMESTAMP("20170101"),
    TIMESTAMP("20170621")
  )
WHERE file.project='django-material'
  and details.installer.name=='pip'
group by 1
order by 1