-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathwebrtc_repo_activity.sql
61 lines (61 loc) · 2.31 KB
/
webrtc_repo_activity.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
# Used to build my master table
SELECT
SUBSTR(_TABLE_SUFFIX, 1, 4) as year,
SUBSTR(_TABLE_SUFFIX, 5,2) as month,
_TABLE_SUFFIX as period,
repo.id AS repoId,
repo.name AS repoName,
actor.id as userId,
actor.login AS user,
org.id as orgId,
org.login AS org,
type as event,
IF
(type="PushEvent"
OR type="PullRequestEvent"
OR type="PullRequestReviewCommentEvent",
TRUE,
FALSE) AS codeEvent,
IF
(type="ForkEvent"
OR type="WatchEvent"
OR type="IssuesEvent"
OR type="IssueCommentEvent",
TRUE,
FALSE) AS popularityEvent,
IF
(REGEXP_CONTAINS(repo.url, r'(?i)webrtc')
OR REGEXP_CONTAINS(payload, r'(?i)webrtc')
OR REGEXP_CONTAINS(other, r'(?i)webrtc'),
TRUE,
FALSE) AS keywordWebrtc,
IF
(REGEXP_CONTAINS(repo.url, r'(?i)getusermedia')
OR REGEXP_CONTAINS(payload, r'(?i)getusermedia')
OR REGEXP_CONTAINS(other, r'(?i)getusermedia'),
TRUE,
FALSE) AS keywordGum,
IF
(REGEXP_CONTAINS(repo.url, r'(?i)\b(stun|turn).?server')
OR REGEXP_CONTAINS(payload, r'(?i)\b(stun|turn).?server')
OR REGEXP_CONTAINS(other, r'(?i)\b(stun|turn).?server'),
TRUE,
FALSE) AS keywordStunTurn,
IF
(REGEXP_CONTAINS(repo.url, r'(?i)peerconnection')
OR REGEXP_CONTAINS(payload, r'(?i)peerconnection')
OR REGEXP_CONTAINS(other, r'(?i)peerconnection'),
TRUE,
FALSE) AS keywordPc,
IF
(REGEXP_CONTAINS(repo.url, r'(?i)rtpsender|rtpreceiver|rtptransceiver|rtcdtlstransport|icetransport|rtctrackevent')
OR REGEXP_CONTAINS(payload, r'(?i)rtpsender|rtpreceiver|rtptransceiver|rtcdtlstransport|icetransport|rtctrackevent')
OR REGEXP_CONTAINS(other, r'(?i)rtpsender|rtpreceiver|rtptransceiver|rtcdtlstransport|icetransport|rtctrackevent'),
TRUE,
FALSE) AS keywordPcExt
FROM
`githubarchive.month.*`
WHERE
REGEXP_CONTAINS(repo.url, r'(?i)webrtc|getusermedia|peerconnection|rtpsender|rtpreceiver|rtptransceiver|rtcdtlstransport|icetransport|rtctrackevent|\b(stun|turn).?server')
OR REGEXP_CONTAINS(payload, r'(?i)webrtc|getusermedia|peerconnection|rtpsender|rtpreceiver|rtptransceiver|rtcdtlstransport|icetransport|rtctrackevent|\b(stun|turn).?server')
OR REGEXP_CONTAINS(other, r'(?i)webrtc|getusermedia|peerconnection|rtpsender|rtpreceiver|rtptransceiver|rtcdtlstransport|icetransport|rtctrackevent|\b(stun|turn).?server')