Analyzing GitHub Pull Request Data with BigQuery

I really enjoyed Felipe Hoffa’s post on Analyzing GitHub issues and comments with BigQuery .

Which got me wondering about my favorite subject ever, The Art of Closing. I wonder what the stats are for the top 15 projects on GitHub in terms of pull requests opened vs. pull requests closed. This post will use the GitHub Archive dataset.

Top 15 repositories with the most pull requests

First let’s find the top 15 repos with the most pull requests from 2015. Let’s make sure to check the payload action is ”opened”.

SELECT
  repo.name,
  COUNT(*) c
FROM
  [githubarchive:year.2015]
WHERE
  type IN ( 'PullRequestEvent')
  AND JSON_EXTRACT(payload, '$.action') IN ('"opened"')
GROUP BY
  repo.name
ORDER BY
  c DESC
LIMIT
  15

Okay that’s a lot of pull requests. Let’s find the projects will the most unique number of pull request authors.

SELECT
  repo.name,
  COUNT(*) c,
  COUNT(DISTINCT actor.id) authors,
FROM
  [githubarchive:year.2015]
WHERE
  type IN ( 'PullRequestEvent')
  AND JSON_EXTRACT(payload, '$.action') IN ('"opened"')
GROUP BY
  repo.name
ORDER BY
  authors DESC
LIMIT
  15

Now let’s see what the merge vs. close numbers look like for those projects.

SELECT
  repo.name,
  COUNT(*) c,
  COUNT(DISTINCT actor.id) authors,
  SUM(CASE WHEN JSON_EXTRACT(payload, '$.pull_request.merged') IN ('true') THEN 1 ELSE 0 END) AS merged,
  SUM(CASE WHEN JSON_EXTRACT(payload, '$.pull_request.merged') IN ('false') THEN 1 ELSE 0 END) AS closed,
FROM
  [githubarchive:year.2015]
WHERE
  type IN ( 'PullRequestEvent')
  AND JSON_EXTRACT(payload, '$.action') IN ('"closed"')
GROUP BY
  repo.name
ORDER BY
  authors DESC
LIMIT
  15

Oh that is super weird. After looking into a few of the repos with 0 merged, it seems they aren’t really using GitHub for merges.

Calculating the merge ratio

So let’s exclude those and try again, this time we can even calculate the merge ratio.

SELECT
  repo.name,
  COUNT(*) c,
  COUNT(DISTINCT actor.id) authors,
  SUM(CASE WHEN JSON_EXTRACT(payload, '$.pull_request.merged') IN ('true') THEN 1 ELSE 0 END) AS merged,
  SUM(CASE WHEN JSON_EXTRACT(payload, '$.pull_request.merged') IN ('false') THEN 1 ELSE 0 END) AS closed,
  ROUND(100*SUM(CASE WHEN JSON_EXTRACT(payload, '$.pull_request.merged') IN ('true') THEN 1 ELSE 0 END)/COUNT(*),2) AS merge_ratio
FROM
  [githubarchive:year.2015]
WHERE
  type IN ( 'PullRequestEvent')
  AND JSON_EXTRACT(payload, '$.action') IN ('"closed"')
GROUP BY
  repo.name
HAVING
  merged > 10
ORDER BY
  authors DESC
LIMIT
  15

Using the diff data

Sweet now let’s see on average what the size of the diffs are for these projects’ pull requests.

SELECT
  repo.name,
  COUNT(*) c,
  COUNT(DISTINCT actor.id) authors,
  SUM(CASE WHEN JSON_EXTRACT(payload, '$.pull_request.merged') IN ('true') THEN 1 ELSE 0 END) AS merged,
  SUM(CASE WHEN JSON_EXTRACT(payload, '$.pull_request.merged') IN ('false') THEN 1 ELSE 0 END) AS closed,
  ROUND(100*SUM(CASE WHEN JSON_EXTRACT(payload, '$.pull_request.merged') IN ('true') THEN 1 ELSE 0 END)/COUNT(*),2) AS merge_ratio,
  AVG(JSON_EXTRACT(payload, '$.pull_request.additions')) AS avg_additions,
  AVG(JSON_EXTRACT(payload, '$.pull_request.deletions')) AS avg_deletions,
  AVG(JSON_EXTRACT(payload, '$.pull_request.changed_files')) AS avg_changed_files,
FROM
  [githubarchive:year.2015]
WHERE
  type IN ( 'PullRequestEvent')
  AND JSON_EXTRACT(payload, '$.action') IN ('"closed"')
GROUP BY
  repo.name
HAVING
  merged > 10
ORDER BY
  authors DESC
LIMIT
  15

Well that’s not all that interesting…

Can we prove you should always keep your pull requests small?

We know that it is always better to make a small pull request to have it merged. Let’s see if we can prove that with data!

SELECT
  repo.name,
  COUNT(*) c,
  COUNT(DISTINCT actor.id) authors,
  ROUND(100*SUM(CASE WHEN JSON_EXTRACT(payload, '$.pull_request.merged') IN ('true') THEN 1 ELSE 0 END)/COUNT(*),2) AS merge_ratio,
  AVG(CASE WHEN JSON_EXTRACT(payload, '$.pull_request.merged') IN ('true') THEN JSON_EXTRACT(payload, '$.pull_request.additions') END) AS merged_avg_additions,
  AVG(CASE WHEN JSON_EXTRACT(payload, '$.pull_request.merged') IN ('true') THEN JSON_EXTRACT(payload, '$.pull_request.deletions') END) AS merged_avg_deletions,
  AVG(CASE WHEN JSON_EXTRACT(payload, '$.pull_request.merged') IN ('true') THEN JSON_EXTRACT(payload, '$.pull_request.changed_files') END) AS merged_avg_changed_files,
  AVG(CASE WHEN JSON_EXTRACT(payload, '$.pull_request.merged') IN ('false') THEN JSON_EXTRACT(payload, '$.pull_request.additions') END) AS closed_avg_additions,
  AVG(CASE WHEN JSON_EXTRACT(payload, '$.pull_request.merged') IN ('false') THEN JSON_EXTRACT(payload, '$.pull_request.deletions') END) AS closed_avg_deletions,
  AVG(CASE WHEN JSON_EXTRACT(payload, '$.pull_request.merged') IN ('false') THEN JSON_EXTRACT(payload, '$.pull_request.changed_files') END) AS closed_avg_changed_files,
FROM
  [githubarchive:year.2015]
WHERE
  type IN ( 'PullRequestEvent')
  AND JSON_EXTRACT(payload, '$.action') IN ('"closed"')
GROUP BY
  repo.name
HAVING
  merge_ratio > 5
ORDER BY
  authors DESC
LIMIT
  15

IT IS PROVEN!!!