Queries that power the open source section of the 2016 Octoverse report https://octoverse.github.com/

active-issues.sql

-- Active issues
-- Count of total active issues in the specified time frame
-- Source: githubarchive public data set via Google BigQuery http://githubarchive.org/

SELECT
	COUNT(DISTINCT JSON_EXTRACT_SCALAR(events.payload, '$.issue.id')) AS events_issue_count
FROM (SELECT * FROM TABLE_DATE_RANGE([githubarchive:day.],TIMESTAMP('2015-09-01'),TIMESTAMP('2016-08-31')))
 AS events

-- 10,723,492 active issues

active-orgs.sql

-- Active Orgs
-- Count of total orgs in which any event was triggered (https://developer.github.com/v3/activity/events/types/) in the specified time frame
-- Source: githubarchive public data set via Google BigQuery http://githubarchive.org/

SELECT
	COUNT(DISTINCT events.org.id) AS events_org_count
FROM (SELECT * FROM TABLE_DATE_RANGE([githubarchive:day.],TIMESTAMP('2015-09-01'),TIMESTAMP('2016-08-31')))
 AS events

-- 331,797 total active organizations

active-repos.sql

-- Active repositories
-- Count of total repositories in which any event was triggered (https://developer.github.com/v3/activity/events/types/) in the specified time frame
-- Source: githubarchive public data set via Google BigQuery http://githubarchive.org/

SELECT
	COUNT(DISTINCT events.repo.id) AS events_repo_count
FROM (SELECT * FROM TABLE_DATE_RANGE([githubarchive:day.],TIMESTAMP('2015-09-01'),TIMESTAMP('2016-08-31')))
 AS events

-- 19,410,242 total active repositories

active-users.sql

-- Active users
-- Count of total users which triggered an event (https://developer.github.com/v3/activity/events/types/) in the specified time frame
-- Source: githubarchive public data set via Google BigQuery http://githubarchive.org/

SELECT
	COUNT(DISTINCT events.actor.id) AS events_actor_count
FROM (SELECT * FROM TABLE_DATE_RANGE([githubarchive:day.],TIMESTAMP('2015-09-01'),TIMESTAMP('2016-08-31')))
 AS events

 -- 5,865,497 total active users

language-count.sql

--- Total Language Count
--- Count of all recognized programming languages on github
--- Source: GitHub Public Data Set https://cloud.google.com/bigquery/public-data/github

SELECT
  COUNT(DISTINCT language.name)
FROM
  [bigquery-public-data:github_repos.languages]

-- 316 total languages

languages-by-pr.sql

-- Most Popular Languages by Pull requests
-- Top programming languages by number of opened pull requests in the specified time frame
-- Source: githubarchive public data set via Google BigQuery http://githubarchive.org/

SELECT
  JSON_EXTRACT_SCALAR(events.payload, '$.pull_request.base.repo.language') AS events_repo_language,
  COUNT(CASE WHEN (events.type = 'PullRequestEvent') THEN 1 ELSE NULL END) AS events_count_pull_request
FROM (
  SELECT
    *
  FROM TABLE_DATE_RANGE([githubarchive:day.],TIMESTAMP('2015-09-01'),TIMESTAMP('2016-08-31'))) AS events
WHERE
  JSON_EXTRACT_SCALAR(events.payload, '$.action') = 'opened'
  AND JSON_EXTRACT_SCALAR(events.payload, '$.pull_request.base.repo.language') IS NOT NULL
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT
  25

-- events_repo_language	events_count_pull_request
-- JavaScript	1604219
-- Java	763783
-- Python	744045
-- Ruby	740610
-- HTML	546211
-- PHP	478153
-- C++	330259
-- CSS	271782
-- C#	229985
-- C	202295
-- Go	188121
-- Shell	143071
-- Objective-C	75478
-- Scala	70216
-- Swift	62284
-- TypeScript	55587
-- CoffeeScript	46216
-- Rust	36874
-- PowerShell	32207
-- Perl	32134
-- Lua	25742
-- R	24909
-- Makefile	24803
-- DM	23531
-- TeX	21782

SELECT
  JSON_EXTRACT_SCALAR(events.payload, '$.pull_request.base.repo.language') AS events_repo_language,
  COUNT(CASE WHEN (events.type = 'PullRequestEvent') THEN 1 ELSE NULL END) AS events_count_pull_request
FROM (
  SELECT
    *
  FROM TABLE_DATE_RANGE([githubarchive:day.],TIMESTAMP('2014-09-01'),TIMESTAMP('2015-08-31'))) AS events
WHERE
  JSON_EXTRACT_SCALAR(events.payload, '$.action') = 'opened'
  AND JSON_EXTRACT_SCALAR(events.payload, '$.pull_request.base.repo.language') IS NOT NULL
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT
  25

-- Repository Language	Events Count Pull Request
-- JavaScript	813911
-- Python	483174
-- Java	467928
-- Ruby	447256
-- PHP	335357
-- C++	223951
-- CSS	199677
-- HTML	159518
-- C	137242
-- C#	122476
-- Go	97420
-- Shell	81393
-- Objective-C	55272
-- Scala	45729
-- CoffeeScript	39161
-- Perl	29687
-- Rust	29199
-- Lua	18466
-- Swift	17193
-- TypeScript	15876
-- R	15576
-- DM	15423
-- Clojure	15046
-- Haskell	14912
-- TeX	13797

license-count.sql

-- Top licenses used on open source projects
-- List of the most commonly used licenses on GitHub open source projects
-- Source: GitHub Public Data Set https://cloud.google.com/bigquery/public-data/github

SELECT
  license,
  COUNT(*)
FROM
  [bigquery-public-data:github_repos.licenses]
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT
  5

orgs-most-contributors.sql

--- Organizations with the most Contributors
--- List of organizations that have the most unique users contributing via commits or opening or commenting on an issue or PR in the specified time frame
--- Source: githubarchive public data set via Google BigQuery http://githubarchive.org/

SELECT
  events.org.login AS events_org_login,
  COUNT(DISTINCT events.actor.id) AS events_actor_count
FROM (
  SELECT
    *
  FROM TABLE_DATE_RANGE([githubarchive:day.],TIMESTAMP('2015-09-01'),TIMESTAMP('2016-08-31'))) AS events
WHERE
  events.type = 'CommitCommentEvent'
    OR events.type = 'IssueCommentEvent'
    OR events.type = 'IssueEvent'
    OR events.type = 'PullRequestEvent'
    OR events.type = 'PullRequestReviewCommentEvent'
    OR events.type = 'PushEvent'
  AND events.org.login IS NOT NULL
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT
  10

-- events_org_login	events_actor_count
-- Microsoft	16419
-- facebook	15682
-- docker	14059
-- angular	12841
-- google	12140
-- atom	9698
-- FortAwesome	9617
-- elastic	7220
-- apache	6999
-- npm	6815

repos-most-contributors.sql

--- Repositories with the most Contributors
--- List of repositories that have the most unique users contributing via commits or opening or commenting on an issue or PR in the specified time frame
--- Source: githubarchive public data set via Google BigQuery http://githubarchive.org/

SELECT
  events.repo.name AS events_repo_name,
  COUNT(DISTINCT events.actor.id) AS events_actor_count
FROM (
  SELECT
    *
  FROM TABLE_DATE_RANGE([githubarchive:day.],TIMESTAMP('2015-09-01'),TIMESTAMP('2016-08-31'))) AS events
WHERE
  events.type = 'CommitCommentEvent'
    OR events.type = 'PushEvent'
    OR events.type = 'IssueCommentEvent'
    OR events.type = 'PullRequestEvent'
    OR events.type = 'PullRequestReviewCommentEvent'
    OR events.type = 'IssuesEvent'
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT
  10

-- events_repo_name	events_actor_count
-- FortAwesome/Font-Awesome	10654
-- docker/docker	8253
-- npm/npm	7041
-- jlord/patchwork	6806
-- facebook/react-native	6250
-- Microsoft/vscode	5855
-- atom/atom	5745
-- FreeCodeCamp/FreeCodeCamp	5622
-- angular/material	4355
-- angular/angular	4217

repos-most-forks.sql

-- Repositories with the most Forks
--- List of repositories that have the most Forks in the specified time frame
--- Source: githubarchive public data set via Google BigQuery http://githubarchive.org/

SELECT
  events.repo.name AS events_repo_name,
  COUNT(DISTINCT events.actor.id) AS events_actor_count
FROM (
  SELECT
    *
  FROM TABLE_DATE_RANGE([githubarchive:day.],TIMESTAMP('2015-09-01'),TIMESTAMP('2016-08-31'))) AS events
  WHERE
    events.type = 'ForkEvent'
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT
  10

-- events_repo_name	events_actor_count
-- jtleek/datasharing	38020
-- octocat/Spoon-Knife	28738
-- rdpeng/ProgrammingAssignment2	27499
-- twbs/bootstrap	15669
-- tensorflow/tensorflow	14070
-- rdpeng/ExData_Plotting1	12458
-- udacity/frontend-nanodegree-resume	11553
-- LarryMad/recipes	10229
-- barryclark/jekyll-now	10070
-- angular/angular.js	9334

repos-most-reviewers.sql

--- Repositories with the most code reviewers
--- List of repositories that have the most unique users reviewing code via PullRequestReviewCommentEvent in the specified time frame
--- Source: githubarchive public data set via Google BigQuery http://githubarchive.org/

SELECT
  events.repo.name AS events_repo_name,
  COUNT(DISTINCT events.actor.id) AS events_actor_count
FROM (
  SELECT
    *
  FROM TABLE_DATE_RANGE([githubarchive:day.],TIMESTAMP('2015-09-01'),TIMESTAMP('2016-08-31'))) AS events
WHERE
  events.type = 'PullRequestReviewCommentEvent'
  AND events.org.login) IS NOT NULL
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT
  10

-- events_repo_name	events_actor_count
-- Homebrew/homebrew	523
-- kubernetes/kubernetes	503
-- apache/spark	478
-- nodejs/node	358
-- rails/rails	341
-- NixOS/nixpkgs	331
-- docker/docker	330
-- rust-lang/rust	329
-- symfony/symfony	323
-- facebook/react-native	307