Skip to content

Instantly share code, notes, and snippets.

@juanpabloaj

juanpabloaj/python_packages_downloaded_by_country.sql

Created September 3, 2016 21:45
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save juanpabloaj/73951664455e585646ee6353cddbeb46 to your computer and use it in GitHub Desktop.
Save juanpabloaj/73951664455e585646ee6353cddbeb46 to your computer and use it in GitHub Desktop.
Download ZIP
Bigquery, Python packages downloaded by country
Raw
python_packages_downloaded_by_country.sql
-- https://bigquery.cloud.google.com/dataset/the-psf:pypi
SELECT concat(
country_code, '_', REGEXP_EXTRACT(details.python, r'^([2-3]).[0-9].*')
) as country_python, count(*) as downloads
FROM (
TABLE_DATE_RANGE([the-psf:pypi.downloads],
TIMESTAMP('2016-01-01'), TIMESTAMP('2016-08-31'))
)
group by country_python order by downloads desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment