Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment