github2.sql

SELECT user, top_repos.repository_url, repository_language, SUM(weight) as weight FROM
  (SELECT repository_url, MAX(repository_watchers)
    FROM [publicdata:samples.github_timeline]
    GROUP BY repository_url
    HAVING MAX(repository_watchers) > 1000) AS top_repos
  JOIN EACH
  (SELECT user, repository_url, repository_language, weight FROM
    (SELECT actor AS user, repository_url, repository_language,
      INTEGER(COUNT(repository_url)) AS weight
      FROM [publicdata:samples.github_timeline]
      WHERE type='PushEvent' 
        AND PARSE_UTC_USEC(created_at) >= PARSE_UTC_USEC('2012-01-01 00:00:00')
        AND PARSE_UTC_USEC(created_at) < PARSE_UTC_USEC('2013-01-01 00:00:00')
      GROUP BY user, repository_url, repository_language),

    (SELECT actor AS user, repository_url, repository_language,
        -COUNT(repository_url) AS weight
      FROM [publicdata:samples.github_timeline]
      WHERE type='PullRequestEvent'
        AND payload_action='closed'
        AND payload_pull_request_merged='true'
        AND PARSE_UTC_USEC(created_at) >= PARSE_UTC_USEC('2012-01-01 00:00:00')
        AND PARSE_UTC_USEC(created_at) < PARSE_UTC_USEC('2013-01-01 00:00:00')
      GROUP BY user, repository_url, repository_language),

    (SELECT payload_pull_request_user_login AS user, repository_url,
        repository_language, INTEGER(COUNT(repository_url)) AS weight
      FROM [publicdata:samples.github_timeline]
      WHERE type='PullRequestEvent'
        AND payload_action='closed'
        AND payload_pull_request_merged='true'
        AND PARSE_UTC_USEC(created_at) >= PARSE_UTC_USEC('2012-01-01 00:00:00')
        AND PARSE_UTC_USEC(created_at) < PARSE_UTC_USEC('2013-01-01 00:00:00')
      GROUP BY user, repository_url, repository_language)
  ) AS events

  ON top_repos.repository_url = events.repository_url
GROUP BY user, top_repos.repository_url, repository_language

-- vim: sw=2 ts=2 sts=2 et