Slide 35
Slide 35 text
merged_pull_requests View
# Merged Pull Request Table
SELECT
JSON_EXTRACT_SCALAR(metadata, '$.pull_request.html_url') AS url,
PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%SZ',JSON_EXTRACT_SCALAR(metadata, '$.pull_request.created_at')) AS time_created,
PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%SZ',JSON_EXTRACT_SCALAR(metadata, '$.pull_request.merged_at')) AS time_merged,
JSON_EXTRACT_SCALAR(metadata, '$.pull_request.merge_commit_sha') AS commit_sha,
JSON_EXTRACT_SCALAR(metadata, '$.pull_request.base.repo.name') AS repository
FROM
`four_keys.events_raw`
WHERE
source = 'github'
AND event_type = 'pull_request'
AND json_extract_scalar(metadata, '$.pull_request.merged') = 'true'
AND json_extract_scalar(metadata, '$.action') = 'closed'
GROUP BY 1,2,3,4,5