BigQuery sql for GitHub Archive dataset: issue close latency for issues opened after 2015-01-01 @ 00:00:00 UTC and closed before 2015-01-31

issuelatency.sql

/* issue close latency for issues opened after 2015-01-01 @ 00:00:00 UTC and closed before 2015-01-31 */ 

SELECT
  REGEXP_REPLACE(repo.name, 'MyOrg/', '') AS repo,
  CEIL((closed_at-created_at)/86400) AS latency,
FROM (
  SELECT
    type,
    repo.name,
    JSON_EXTRACT(payload, '$.action') AS event,
    TIMESTAMP_TO_SEC(TIMESTAMP(REGEXP_REPLACE(JSON_EXTRACT(payload, '$.issue.created_at'), '\"|T|Z', ' '))) AS created_at,
    TIMESTAMP_TO_SEC(TIMESTAMP(REGEXP_REPLACE(JSON_EXTRACT(payload, '$.issue.closed_at'), '\"|T|Z', ' '))) AS closed_at,
  FROM (TABLE_DATE_RANGE([githubarchive:day.events_], TIMESTAMP('2015-01-01'), TIMESTAMP('2015-01-31') ))
  WHERE
    type = 'IssuesEvent'
    AND repo.name CONTAINS 'MyOrg/'
    AND NOT REGEXP_MATCH(repo.name,'^MyOrg/SomeRepo$') )
WHERE
  event CONTAINS 'closed'
  AND NOT created_at < 1420070400 /* 1/1/2015 @ 00:00:00 UTC */
ORDER BY
  repo.name,
  latency