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

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