WITH
  close_events AS (
    SELECT payload
    FROM `githubarchive.day.201803*`
    WHERE
      _TABLE_SUFFIX BETWEEN '01' AND '09'
      AND type = 'IssuesEvent'
      AND JSON_EXTRACT_SCALAR(payload, '$.action') = 'closed'
      AND JSON_EXTRACT_SCALAR(payload, '$.issue.body') != 'null'
  ),
  nested_labels AS (
    SELECT
      JSON_EXTRACT_ARRAY(payload, '$.issue.labels') AS labels,
      JSON_EXTRACT_SCALAR(payload, '$.issue.title') AS title,
      JSON_EXTRACT_SCALAR(payload, '$.issue.body') AS body
    FROM close_events
  ),
  labels AS (
    SELECT 
      LOWER(JSON_EXTRACT_SCALAR(label, '$.name')) AS label,
      title,
      body 
    FROM 
      nested_labels
      CROSS JOIN UNNEST(nested_labels.labels) as label
  ),
  top3_labels AS (
    SELECT *
    FROM labels
    WHERE (label = 'bug' OR label = 'enhancement' OR label = 'question') 
  )
SELECT CONCAT('__label__', label, ' ', title, ' ', REGEXP_REPLACE(body, '(\r|\n|\r\n)',' ')) FROM top3_labels;
