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,, actor.login, org.login,JSON_EXTRACT(payload, '$') 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_],
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