Skip to content

Instantly share code, notes, and snippets.

@sAbakumoff
Created January 30, 2017 06:44
Show Gist options
  • Save sAbakumoff/42ee44bc7b6c5ebbf195d31119d5a2cd to your computer and use it in GitHub Desktop.
Save sAbakumoff/42ee44bc7b6c5ebbf195d31119d5a2cd to your computer and use it in GitHub Desktop.
SELECT
q.id id,
q.title title,
q.creation_date creation_date,
t.repo repo,
t.branch branch,
t.path path,
t.ext ext,
t.line_number line_number
FROM
[bigquery-public-data:stackoverflow.posts_questions] q
JOIN (
SELECT *
FROM (
SELECT
a.parent_id AS question_id,
b.repo AS repo,
b.branch as branch,
b.path as path,
b.ext as ext,
b.line_number as line_number
FROM
[bigquery-public-data:stackoverflow.posts_answers] a
JOIN
(select Integer(answer_id) answer_id, repo, branch, path, ext, line_number
from [Dataset1.so_ref_top_repos_top_langs] where answer_id is not null) b
ON
a.id = b.answer_id),
(
SELECT
a.id AS question_id,
b.repo AS repo,
b.branch as branch,
b.path as path,
b.ext as ext,
b.line_number as line_number
FROM
[bigquery-public-data:stackoverflow.posts_questions] a
JOIN
(select Integer(question_id) question_id, repo, branch, path, ext, line_number
from [Dataset1.so_ref_top_repos_top_langs] where question_id is not null) b
ON
a.id = b.question_id) ) t
ON
t.question_id = q.id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment