GitHub Archive Queries Used - GoldenLions/GitHubScout GitHub Wiki

languages: Activity over Time
language_activity_by_month

SELECT repository_language, CONCAT(LEFT(created_at, 7), '-01') as month, COUNT(*) as activity
FROM [githubarchive:github.timeline]
WHERE
(type='PublicEvent' OR type='PushEvent' OR type='WatchEvent' OR type = 'PullRequestEvent'
OR type = 'CreateEvent' or type ="IssuesEvent" or type ="ForkEvent")
GROUP BY repository_language, month
ORDER BY month DESC

languages: Repos Created over time
language_creates_by_month

SELECT repository_language, CONCAT(LEFT(created_at, 7), '-01') as month, COUNT(*) as creates
FROM [githubarchive:github.timeline]
WHERE
type='CreateEvent'
GROUP BY repository_language, month
ORDER BY month DESC

languages: repos made public over time
language_made_public_by_month

SELECT repository_language, CONCAT(LEFT(created_at, 7), '-01') as month, COUNT(*) as public_repos FROM [githubarchive:github.timeline]
WHERE
type='PublicEvent'
GROUP BY repository_language, month
ORDER BY month DESC

language: pushes over time
language_pushes_by_month

SELECT repository_language, CONCAT(LEFT(created_at, 7), '-01') as month, COUNT(*) as pushes
FROM [githubarchive:github.timeline]
WHERE
type='PushEvent'
GROUP BY repository_language, month
ORDER BY month DESC

==

home: Top 10 Languages Across Github (quarterly) home_top_languages_by_activity_quarterly

SELECT repository_language, CONCAT(LEFT(created_at, 7), '-01') as date, COUNT(*) as activity
FROM [githubarchive:github.timeline]
WHERE
(type='PublicEvent' OR type='PushEvent' OR type='WatchEvent' OR type = 'PullRequestEvent'
OR type = 'CreateEvent' or type ="IssuesEvent" or type ="ForkEvent")
and repository_language is not null
AND PARSE_UTC_USEC(created_at) >= NOW() - 7776000000000
AND PARSE_UTC_USEC(created_at) < NOW()
GROUP BY repository_language, date
ORDER BY date DESC, activity DESC
LIMIT 10

home: All Languages
home_all_languages

SELECT repository_language, COUNT(*) as activity
FROM [githubarchive:github.timeline]
where (type='PublicEvent' OR type='PushEvent' OR type='WatchEvent' OR type = 'PullRequestEvent' OR type = 'CreateEvent' or type ="IssuesEvent" or type ="ForkEvent") GROUP EACH BY repository_language
HAVING repository_language IS NOT NULL
AND repository_language != ''
ORDER BY activity DESC

home: Top 20 Countries by Activity
home_top_languages_by_activity Big query

SELECT repository_language, actor_attributes_location,
COUNT(DISTINCT actor) as users, COUNT(*) as events
FROM [githubarchive:github.timeline]
WHERE
(type='PublicEvent' OR type='PushEvent' OR type='WatchEvent' OR type = 'PullRequestEvent'
OR type = 'CreateEvent' OR type ="IssuesEvent" OR type ="ForkEvent")
GROUP EACH BY repository_language, actor_attributes_location
HAVING repository_language IS NOT NULL
AND repository_language != ''
AND actor_attributes_location IS NOT NULL
AND actor_attributes_location != ''
AND users >= 1
ORDER BY users DESC

home: Top 20 Countries by Activity
home__top_languages_by_activity our normalized database

select country, sum(events) as events, sum(users) as users, language from github_locations where country is not null group by country, language

===

Not used.

users_by_activity_and_language:

SELECT actor, repository_language, COUNT(*) as events
FROM [githubarchive:github.timeline]
GROUP EACH BY actor, repository_language
ORDER BY events DESC

distinct_users_pushing_by_month_and_language:

SELECT repository_language, LEFT(created_at, 7) as month, COUNT(DISTINCT actor) as users
FROM [githubarchive:github.timeline]
WHERE
type='PushEvent'
GROUP EACH BY repository_language, month
ORDER BY month DESC

SELECT NOW() -7776000000000 , now() , SEC_TO_TIMESTAMP (1403847178508094 /1000000)