output.csv

tzfpy-download-os-compare.sql

#standardSQL
WITH Period1 AS (
  SELECT
    details.distro.name AS distribution,
    COUNT(*) AS downloads_period1
  FROM
    `bigquery-public-data.pypi.file_downloads`
  WHERE
    file.project = 'tzfpy'
    AND DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 15 DAY)
    AND details.distro.name IS NOT NULL
  GROUP BY
    distribution
),
Period2 AS (
  SELECT
    details.distro.name AS distribution,
    COUNT(*) AS downloads_period2
  FROM
    `bigquery-public-data.pypi.file_downloads`
  WHERE
    file.project = 'tzfpy'
    AND DATE(timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) AND CURRENT_DATE()
    AND details.distro.name IS NOT NULL
  GROUP BY
    distribution
)
SELECT
  p1.distribution,
  p1.downloads_period1,
  IFNULL(p2.downloads_period2, 0) AS downloads_period2,
  IFNULL(p2.downloads_period2, 0) - p1.downloads_period1 AS change_in_downloads
FROM
  Period1 p1
LEFT JOIN
  Period2 p2
ON
  p1.distribution = p2.distribution
ORDER BY
  change_in_downloads DESC;