Skip to content

Instantly share code, notes, and snippets.

@benrudolph

benrudolph/all_emoticons.sql

Created January 15, 2016 04:28
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save benrudolph/fe1df5aea36f3b8f57e2 to your computer and use it in GitHub Desktop.
Save benrudolph/fe1df5aea36f3b8f57e2 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
all_emoticons.sql
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