github-stats.sql

/* GitHub query to get the number of comments, PR, releases, etc. for a given GH org */

WITH 
  ProjectData AS (SELECT * FROM `githubarchive.day.2017*` WHERE repo.name LIKE 'fastlane/%'),
  Actors AS (SELECT DISTINCT(actor.login) AS login FROM ProjectData)

SELECT * FROM (
  SELECT 
    actors.login,
    (SELECT COUNT(*) FROM ProjectData WHERE type = 'IssueCommentEvent' AND actor.login = actors.login) AS Comments,
    (SELECT COUNT(*) FROM ProjectData WHERE type = 'PullRequestEvent' AND actor.login = actors.login) AS PRs,
    (SELECT COUNT(*) FROM ProjectData WHERE type = 'PullRequestReviewCommentEvent' AND actor.login = actors.login) AS ReviewComments,
    (SELECT COUNT(*) FROM ProjectData WHERE type = 'ReleaseEvent' AND actor.login = actors.login) AS Releases,
    (SELECT COUNT(*) FROM ProjectData WHERE type = 'IssuesEvent' AND actor.login = actors.login) AS ClosedRenamedAndLabeledIssues
  FROM Actors as actors
)
WHERE PRs > 0 OR Comments > 0
ORDER BY PRs DESC, Comments DESC;