用 BigQuery 查询 GitHub 上的 'Repositories contributed to'

在 Google BigQuery 上查,不考虑免费额度,大约每次 7 美元

WITH user_name AS (SELECT 'crvv' UNION ALL SELECT 'CRVV'),
data AS (
SELECT
  type,
  repo.name AS repo_name,
  regexp_extract(repo.name, '(.*)/.*') AS repo_owner,
  coalesce(
    JSON_EXTRACT_SCALAR(payload, '$.pull_request.html_url'),
    JSON_EXTRACT_SCALAR(payload, '$.issue.html_url')
  ) AS url,
  coalesce(
    JSON_EXTRACT_SCALAR(payload, '$.pull_request.created_at'),
    JSON_EXTRACT_SCALAR(payload, '$.issue.created_at')
  ) AS time
FROM
  `githubarchive.month.201*`
WHERE
  _TABLE_SUFFIX > '50'
  AND type IN ('IssuesEvent', 'PullRequestEvent')
  AND actor.login IN (SELECT * FROM user_name)
  AND JSON_EXTRACT_SCALAR(payload, '$.action') = 'opened'
)
SELECT * FROM data
  WHERE repo_owner NOT IN (SELECT * FROM user_name)
  ORDER BY time