2016 Top Repositories by Pull Requests Opened

Top Apache Repositories by PRs Opened since Apache Beam started incubation.sql

WITH since_incubation AS (
   SELECT * FROM `githubarchive.day.201*`
   WHERE _TABLE_SUFFIX > '60131' -- after 2016-01-31 since Beam started incubating on 2016-02-01
),
pull_requests AS (
  SELECT
    -- map a podling to its final name
    replace(e.repo.name, "incubator-", "") AS repo,
    COUNT(*) AS pr_count
  FROM since_incubation AS e
  WHERE e.type = 'PullRequestEvent' AND STARTS_WITH(e.payload, '{"action":"opened"')
    AND e.org.login = 'apache'
  GROUP BY repo
)
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