most_commits_per_user_from_nepal.sql

  --
  -- Most commits per user from Jan 2016 to March 2017
  -- More than one project can share the same commits if one is a fork of the other
  --
  -- https://bigquery.cloud.google.com/savedquery/39178653524:a0ff27f9332c4ef2b03ecea1c8dbd3e2
  --
SELECT
  u.login AS login,
  u.location AS location,
  COUNT(c.id) AS num_commits
FROM
  [ghtorrent-bq.ght_2017_04_01.project_commits] pc
JOIN (
  SELECT
    id,
    author_id
  FROM
    [ghtorrent-bq.ght_2017_04_01.commits]
  WHERE
    DATE(created_at) BETWEEN DATE('2016-01-01')
    AND DATE('2017-04-01')) c
ON
  pc.commit_id = c.id
JOIN (
  SELECT
    login,
    location,
    id
  FROM
    [ghtorrent-bq.ght_2017_04_01.users]
  WHERE
    country_code = 'np') u
ON
  c.author_id = u.id,
GROUP BY
  login,
  location
ORDER BY
  num_commits DESC;