most_commits_per_user_in_javascript_projects_from_nepal.sql

  --
  -- Most commits per user in JavaScript projects from Jan 2016 to March 2017
  -- Change JavaScript to any other language for other metrics
  --
  -- https://bigquery.cloud.google.com/savedquery/39178653524:00053da8b5534c24881a08d034de9bae
  --
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
    id
  FROM
    [ghtorrent-bq.ght_2017_04_01.projects]
  WHERE
    language = 'JavaScript') p
ON
  p.id = pc.project_id
JOIN (
  SELECT
    login,
    location,
    id
  FROM
    [ghtorrent-bq.ght.users]
  WHERE
    country_code = 'np') u
ON
  c.author_id = u.id,
GROUP BY
  login,
  location
ORDER BY
  num_commits DESC;