Skip to content

Instantly share code, notes, and snippets.

@di
Created August 24, 2022 20:57
Show Gist options
  • Save di/224a035f1eed672a703bc076fc689a4e to your computer and use it in GitHub Desktop.
Save di/224a035f1eed672a703bc076fc689a4e to your computer and use it in GitHub Desktop.
BigQuery query to measure download counts of projects compromised by PyPI's phishing attack
# Using https://packaging.python.org/en/latest/guides/analyzing-pypi-package-downloads/
SELECT
file.project,
file.version,
COUNT(*) AS total_downloads
FROM
`bigquery-public-data.pypi.file_downloads`
WHERE
file.project IN ('exotel',
'spam',
'deep-translator')
AND file.version IN ('0.1.6',
'2.0.2',
'4.0.2',
'1.8.5')
AND DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
AND CURRENT_DATE()
GROUP BY
file.project,
file.version
ORDER BY
total_downloads DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment