BigQuery scheduled query for daily marshmallow downloads

daily-downloads.sql

WITH
  dls AS (
  SELECT
    DATE_SUB(DATE(@run_time), INTERVAL 1 DAY) AS date,
    file.project AS package,
    details.installer.name AS installer,
    details.python AS python_version,
    CAST(SPLIT(details.python, '.')[
    OFFSET
      (0)] AS string) AS python_major,
    CAST(CONCAT(SPLIT(details.python, '.')[
      OFFSET
        (0)],'.',SPLIT(details.python, '.')[
      OFFSET
        (1)]) AS string) AS python_minor,
    file.version AS marshmallow_version,
    CAST(SPLIT(file.version, '.')[
    OFFSET
      (0)] AS string) AS marshmallow_major,
    details.system.name AS system
  FROM
    `the-psf.pypi.downloads*`
  WHERE
    _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(DATE(@run_time), INTERVAL 1 DAY))
    AND file.project = 'marshmallow'
    -- Exclude mirrors
    AND details.installer.name NOT IN ("bandersnatch",
      "z3c.pypimirror",
      "Artifactory",
      "devpi")
    AND details.python IS NOT NULL )
SELECT
  date,
  'python_major' AS category_label,
  python_major AS category_value,
  COUNT(*) AS downloads
FROM
  dls
GROUP BY
  date,
  package,
  category_value
UNION ALL
SELECT
  date,
  'python_minor' AS category_label,
  python_minor AS category_value,
  COUNT(*) AS downloads
FROM
  dls
GROUP BY
  date,
  package,
  category_value
UNION ALL
SELECT
  date,
  'marshmallow_major' AS category_label,
  marshmallow_major AS category_value,
  COUNT(*) AS downloads
FROM
  dls
WHERE
  marshmallow_major IN ('2',
    '3')
GROUP BY
  date,
  package,
  category_value
UNION ALL
SELECT
  date,
  'combined' AS category_label,
  CAST(CONCAT('py', python_minor, '-', 'marshmallow', marshmallow_major) AS string) AS category_value,
  COUNT(*) AS downloads
FROM
  dls
WHERE
  marshmallow_major IN ('2',
    '3')
GROUP BY
  date,
  package,
  category_value
UNION ALL
SELECT
  date,
  'marshmallow_version' AS category_label,
  marshmallow_version AS category_value,
  COUNT(*) AS downloads
FROM
  dls
WHERE marshmallow_major IN ('2',
    '3')
GROUP BY
  date,
  package,
  category_value
  ------------ Excluding Linux (CI downloads) --------
UNION ALL
SELECT
  date,
  'python_major' AS category_label,
  CONCAT(python_major, '-', 'no_linux') AS category_value,
  COUNT(*) AS downloads
FROM
  dls
WHERE
  system != "Linux"
GROUP BY
  date,
  package,
  category_value
UNION ALL
SELECT
  date,
  'python_minor' AS category_label,
  CONCAT(python_minor, '-', 'no_linux') AS category_value,
  COUNT(*) AS downloads
FROM
  dls
WHERE
  system != 'Linux'
GROUP BY
  date,
  package,
  category_value
UNION ALL
SELECT
  date,
  'marshmallow_major' AS category_label,
  CONCAT(marshmallow_major, '-', 'no_linux') AS category_value,
  COUNT(*) AS downloads
FROM
  dls
WHERE
  marshmallow_major IN ('2',
    '3')
  AND system != 'Linux'
GROUP BY
  date,
  package,
  category_value
UNION ALL
SELECT
  date,
  'combined' AS category_label,
  CAST(CONCAT('py', python_minor, '-', 'marshmallow', marshmallow_major, '-', 'no_linux') AS string) AS category_value,
  COUNT(*) AS downloads
FROM
  dls
WHERE
  marshmallow_major IN ('2',
    '3')
  AND system != 'Linux'
GROUP BY
  date,
  package,
  category_value
UNION ALL
SELECT
  date,
  'marshmallow_version' AS category_label,
  CAST(CONCAT(marshmallow_version, '-', 'no_linux') AS string) AS category_value,
  COUNT(*) AS downloads
FROM
  dls
WHERE
  marshmallow_major IN ('2',
    '3') AND
  system != 'Linux'
GROUP BY
  date,
  package,
  category_value