2016 Top Repositories by Pull Requests Opened

2016 Top Repositories by Pull Requests Opened.sql

WITH pull_requests AS (
  SELECT
    e.repo.name AS repo,
    COUNT(*) AS pr_count
  FROM `githubarchive.day.2016*` AS e
  WHERE e.type = 'PullRequestEvent' AND STARTS_WITH(e.payload, '{"action":"opened"')
  GROUP BY e.repo.name
)
SELECT
  pr.repo,
  pr.pr_count,
  RANK() OVER w AS `rank`,
  100 * PERCENT_RANK() OVER w AS percentile
FROM pull_requests pr
WINDOW w AS (ORDER BY pr.pr_count DESC)
ORDER BY pr.pr_count DESC
LIMIT 200