Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
select *, REGEXP_REPLACE(REGEXP_REPLACE(word, r'^[^:]*:', ''), r':[^:]*$', '') as emoticon from (
SELECT type, repo.name, actor.login, org.login,JSON_EXTRACT(payload, '$.comment.id') as comment_id, JSON_EXTRACT(payload, '$.comment.url') as url, SPLIT(JSON_EXTRACT(payload, '$.comment.body'), ' ') as word, MONTH(TIMESTAMP(REGEXP_REPLACE(JSON_EXTRACT(JSON_EXTRACT(payload, '$.comment'), '$.created_at'), r'"|T.*', ''))) as m
FROM (TABLE_DATE_RANGE([githubarchive:day.events_],
TIMESTAMP('2015-03-01'),
TIMESTAMP('2015-03-10')
))
WHERE (type = 'IssueCommentEvent' OR type = 'PullRequestReviewCommentEvent' OR type = 'CommitCommentEvent') AND org.login = 'dimagi' AND actor.login <> 'dimagimon'
)
where REGEXP_MATCH(word, r':[\w]+:')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment