get_ref.sql

SELECT
  files.id AS id,
  FIRST(files.repo_name) AS repo,
  REGEXP_EXTRACT(FIRST(files.ref), r"refs/heads/(.*)$") AS branch,
  FIRST(files.path) AS path,
  REGEXP_EXTRACT(FIRST(files.path), r'\.([^\.]*)$') AS ext,
  FIRST(lines.line) AS line,
  REGEXP_EXTRACT(FIRST(lines.line), r'stackoverflow.com/(?:q|questions)/([0-9]+)') question_id,
  REGEXP_EXTRACT(FIRST(lines.line), r'stackoverflow.com/(?:a|answers)/([0-9]+)') answer_id,
  STRING(FIRST(lines.pos)) AS line_number
FROM
  [bigquery-public-data:github_repos.files] files
JOIN (
  SELECT
    line,
    id,
    POSITION(line) AS pos
  FROM (
    SELECT
      id,
      SPLIT(REPLACE(content, "\n", " \n"), "\n") AS line
    FROM
      [fh-bigquery:github_extracts.contents_top_repos_top_langs] )) lines
ON
  lines.id = files.id
WHERE
  lines.line LIKE '%stackoverflow.com%'
GROUP BY
  id