queries.sql

--Number of commits for libraries of interest during 2015–09–30 ~ 2016–09–30:

SELECT date, idname.repo_name AS repo_name, num_commits
FROM
(SELECT 
  DATE(created_at) AS date, 
  repo.id,
  SUM(INTEGER(JSON_EXTRACT(payload, '$.size'))) AS num_commits
FROM
  (TABLE_DATE_RANGE([githubarchive:day.],
     TIMESTAMP('2015-09-30'),
     TIMESTAMP('2016-09-30')
  ))
WHERE
  type = 'PushEvent'
  AND repo.id IN (29986727, 474633, 
                    2888818, 7035651, 23620078,
                    130013, 14135470, 8609125,
                    17165658, 2211243)

GROUP BY date, repo.id 
ORDER BY date ASC ) AS results
INNER JOIN [github-explorations:example.repo_name_id] AS idname
ON results.repo.id = idname.repo_id


--PRs opened for libraries of interest during 2015–09–30 ~ 2016–09–30:

SELECT date, idname.repo_name AS repo_name, num_PR
FROM
(SELECT 
  DATE(created_at) AS date, 
  repo.id,
  count(*) AS num_PR
FROM
  (TABLE_DATE_RANGE([githubarchive:day.],
     TIMESTAMP('2015-09-30'),
     TIMESTAMP('2016-09-30')
  ))
WHERE
  type = 'PullRequestEvent'
  AND JSON_EXTRACT(payload, '$.action') = '\"opened\"'
  AND repo.id IN (29986727, 474633, 
                    2888818, 7035651, 23620078,
                    130013, 14135470, 8609125,
                    17165658, 2211243)

GROUP BY date, repo.id 
ORDER BY date ASC ) AS results
INNER JOIN [github-explorations:example.repo_name_id] AS idname
ON results.repo.id = idname.repo_id


--New watchers for libraries of interest during 2015–09–30 ~ 2016–09–30:

SELECT date, idname.repo_name AS repo_name, num_watchers
FROM
(SELECT 
  DATE(created_at) AS date, 
  repo.id,
  count(*) AS num_watchers
FROM
  (TABLE_DATE_RANGE([githubarchive:day.],
     TIMESTAMP('2015-09-30'),
     TIMESTAMP('2016-09-30')
  ))
WHERE
  type = 'WatchEvent'
  AND repo.id IN (29986727, 474633, 
                    2888818, 7035651, 23620078,
                    130013, 14135470, 8609125,
                    17165658, 2211243)

GROUP BY date, repo.id 
ORDER BY date ASC ) AS results
INNER JOIN [github-explorations:example.repo_name_id] AS idname
ON results.repo.id = idname.repo_id