BigQuery query to measure download counts of projects compromised by PyPI's phishing attack

compromised-download-counts.sql

  # 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