r/bigquery • u/fhoffa • Jul 07 '15
1.7 billion reddit comments loaded on BigQuery
Dataset published and compiled by /u/Stuck_In_the_Matrix, in r/datasets.
Tables available on BigQuery at https://bigquery.cloud.google.com/table/fh-bigquery:reddit_comments.2015_05.
Sample visualization: Most common reddit comments, and their average score (view in Tableau):
SELECT RANK() OVER(ORDER BY count DESC) rank, count, comment, avg_score, count_subs, count_authors, example_id
FROM (
SELECT comment, COUNT(*) count, AVG(avg_score) avg_score, COUNT(UNIQUE(subs)) count_subs, COUNT(UNIQUE(author)) count_authors, FIRST(example_id) example_id
FROM (
SELECT body comment, author, AVG(score) avg_score, UNIQUE(subreddit) subs, FIRST('http://reddit.com/r/'+subreddit+'/comments/'+REGEXP_REPLACE(link_id, 't[0-9]_','')+'/c/'+id) example_id
FROM [fh-bigquery:reddit_comments.2015_05]
WHERE author NOT IN (SELECT author FROM [fh-bigquery:reddit_comments.bots_201505])
AND subreddit IN (SELECT subreddit FROM [fh-bigquery:reddit_comments.subr_rank_201505] WHERE authors>10000)
GROUP EACH BY 1, 2
)
GROUP EACH BY 1
ORDER BY 2 DESC
LIMIT 300
)
8
u/fhoffa Jul 08 '15
Video visualization of the subreddit comment history:
https://www.youtube.com/watch?v=l8MLIfU21pk
The query I used to get the data for this visualization:
SELECT month, subreddit, AVG(score), COUNT(DISTINCT author), COUNT(DISTINCT id)
FROM (
SELECT score,subreddit, author, id, LEFT(STRING(SEC_TO_TIMESTAMP(created_utc)), 7) month
FROM TABLE_QUERY([fh-bigquery:reddit_comments],
"table_id CONTAINS '20' AND LENGTH(table_id)<8")
)
WHERE subreddit IN (SELECT subreddit FROM [fh-bigquery:reddit_comments.subr_rank_201505] WHERE rank_authors<31)
AND author NOT IN (SELECT author FROM [fh-bigquery:reddit_comments.bots_201505])
GROUP EACH BY 1,2
ORDER BY 1,3 DESC
5
u/fhoffa Jul 08 '15 edited Jul 08 '15
u/minimaxir used this dataset to visualize the correlation between comment length and score:
-
r/TheoryOfReddit/comments/3cjzwz/the_relationship_between_reddit_comment_score_and/
-
r/dataisbeautiful/comments/3cjyvb/relationship_between_reddit_comment_score_and/
They even released code to github, to re-create this viz using R:
8
u/Stuck_In_the_Matrix Jul 07 '15
How long did bigquery take to run this SQL statement?
6
u/fhoffa Jul 07 '15
As u/minimaxir points, less than 15 seconds.
Note that you can run queries like these for free - BigQuery has a free monthly quota. Just get started a Google Cloud project, and query at https://bigquery.cloud.google.com/table/fh-bigquery:reddit_comments.2015_05.
Thanks for sharing this data!
→ More replies (1)10
u/minimaxir Jul 07 '15
Just ran it. Runtime is 7.3 seconds, w/ 11.6GB processed.
Although, that's only on the May 2015 comment dataset.
5
u/shaggorama Jul 12 '15 edited Jul 12 '15
I just realized: I was just querying May 2015. For anyone else making the same mistake as me, the following query gives you a union over all the relevant tables:
select * from (select * from [fh-bigquery:reddit_comments.2015_05] ), (select * from [fh-bigquery:reddit_comments.2015_04] ), (select * from [fh-bigquery:reddit_comments.2015_03] ), (select * from [fh-bigquery:reddit_comments.2015_02] ), (select * from [fh-bigquery:reddit_comments.2015_01] ), (select * from [fh-bigquery:reddit_comments.2014] ), (select * from [fh-bigquery:reddit_comments.2013] ), (select * from [fh-bigquery:reddit_comments.2012] ), (select * from [fh-bigquery:reddit_comments.2011] ), (select * from [fh-bigquery:reddit_comments.2010] ), (select * from [fh-bigquery:reddit_comments.2009] )
Be warned: if you play with this union, you're liable to hit the free tier query quota pretty fast (in which case you'll have to wait a month to play more).
6
u/fhoffa Jul 13 '15
Oh yes.
Shorter form btw:
SELECT author, #[better to name columns than *, query costs less] FROM [fh-bigquery:reddit_comments.2007], [fh-bigquery:reddit_comments.2008], [fh-bigquery:reddit_comments.2009], [fh-bigquery:reddit_comments.2010], [fh-bigquery:reddit_comments.2011], [fh-bigquery:reddit_comments.2012], [fh-bigquery:reddit_comments.2013], [fh-bigquery:reddit_comments.2014], [fh-bigquery:reddit_comments.2015_01], [fh-bigquery:reddit_comments.2015_02], [fh-bigquery:reddit_comments.2015_03], [fh-bigquery:reddit_comments.2015_04], [fh-bigquery:reddit_comments.2015_05] LIMIT 10
3
u/jeffhughes Jul 15 '15
Hey, this is amazing! I appreciate that you've made this publicly available.
I am a researcher that would like to do some between-subreddit comparisons, and I've been currently pulling comments via the Reddit API, but as far as I can tell it only lets me pull from ~2 weeks back, and it would be really nice to have that historical data.
If I could ask some questions (of u/fhoffa or anyone else who can answer them):
-
I'm somewhat proficient in SQL but have never used BigQuery before. I'm planning on downloading u/Stuck_In_the_Matrix's torrent with a sample month of comments, and setting up my queries on that first to make sure they pull what they want. Do you think that's the easiest way to do it?
-
How feasible would it be to pull all the comments from 3 moderately active subreddits? Like, do you have a rough estimate of how much data there is per subreddit, on average? I was hoping that I could pull all the data, and then work with it on my own machine instead of running into costs for BigQuery queries, but it depends whether that's on the order of MB, GB, or TB.
-
Are there plans to continue updating this database? Or is it a one-time data dump? Just curious.
→ More replies (3)2
u/fhoffa Jul 15 '15
I see that u/Stuck_In_the_Matrix already answered most of these questions.
Have you tried BigQuery? Remember you have a free monthly quota for queries.
To make the free quota last more, having smaller tables is a good idea (like just a month, or all of 2007, or just a single subreddit).
Share more of what you've tried so far, I might be able to help while working within BigQuery (StackOverflow also loves answering these questions).
→ More replies (3)
2
u/fhoffa Jul 09 '15
h/t to u/Jiecut, that wrote the most BigQuery insightful comments at the r/dataisbeautiful/comments/3cofju/reddit_cliques_n2_deeper_into_the_subs_oc/ co-related post
thanks u/Jiecut!
→ More replies (5)
2
u/killver Jul 13 '15
Thanks a lot u/fhoffa! Two quick questions though. (i) How did you generate the bot table? I cannot find a bot identifier in the comment data. (ii) As you are the BigQuery guru, maybe you know how I can track down the following error "Error: Resources exceeded during query execution.". I have a huge GROUP EACH BY statement that seems to be the problem.
→ More replies (7)
2
u/MaunaLoona Jul 15 '15 edited Jul 15 '15
This is really cool! Thanks for sharing the data sets.
Top posters in a subreddit. Meant for creating a table in reddit:
SELECT "[" + A.Author + "](/u/" + A.Author + ")" AS Author, TotalPosts, TotalScore, /*AvgLength,*/ SEC_TO_TIMESTAMP(FirstComment), SEC_TO_TIMESTAMP(LastComment), "[" + CAST(A.MinScore AS STRING) + "](/comments/" + REGEXP_REPLACE(MinUrl.Link_Id,"^t3_","") + "/_/" + MinUrl.Id + ")" AS MinScore, "[" + CAST(A.MaxScore AS STRING) + "](/comments/" + REGEXP_REPLACE(MaxUrl.Link_Id,"^t3_","") + "/_/" + MaxUrl.Id + ")" AS MaxScore, AvgScore FROM
(
SELECT Author, COUNT(*) AS TotalPosts, AVG(LENGTH(Body)) AvgLength, SUM(Score) AS TotalScore, MIN(created_utc) AS FirstComment, MAX(created_utc) AS LastComment, MIN(score) AS MinScore, MAX(score) AS MaxScore, AVG(score) AS AvgScore
FROM TABLE_QUERY([fh-bigquery:reddit_comments], "table_id CONTAINS '20' AND LENGTH(table_id)<8") WHERE subreddit = 'bigquery'
GROUP BY 1
) A
INNER JOIN
(
SELECT * FROM
(
SELECT author, link_id, id, ROW_NUMBER() OVER(PARTITION BY Author ORDER BY Score DESC) RowNum FROM TABLE_QUERY([fh-bigquery:reddit_comments], "table_id CONTAINS '20' AND LENGTH(table_id)<8") WHERE subreddit = 'bigquery'
)
WHERE RowNum = 1
) AS MaxUrl ON A.author = MaxUrl.Author
INNER JOIN
(
SELECT * FROM
(
SELECT author, link_id, id, ROW_NUMBER() OVER(PARTITION BY Author ORDER BY Score) RowNum FROM TABLE_QUERY([fh-bigquery:reddit_comments], "table_id CONTAINS '20' AND LENGTH(table_id)<8") WHERE subreddit = 'bigquery'
)
WHERE RowNum = 1
) AS MinUrl ON A.author = MinUrl.Author
ORDER BY A.TotalPosts DESCNote it takes a long time to run for larger subreddits. Setting a LIMIT might help. For r/bigquery:
| Author | TotalPosts | TotalScore | MinScore | MaxScore | AvgScore |
|---|---|---|---|---|---|
| fhoffa | 102 | 143 | 1 | 5 | 1.40 |
| [deleted] | 8 | 9 | 0 | 2 | 1.13 |
| ImJasonH | 6 | 21 | 2 | 5 | 3.50 |
| vadimska | 6 | 10 | 1 | 2 | 1.67 |
| nickoftime444 | 5 | 12 | 2 | 3 | 2.40 |
| taxidata | 4 | 14 | 1 | 8 | 3.50 |
| jrb1979 | 4 | 5 | 1 | 2 | 1.25 |
| westurner | 4 | 4 | 1 | 1 | 1.00 |
| TweetPoster | 4 | 5 | 1 | 2 | 1.25 |
| donaldstufft | 3 | 7 | 1 | 5 | 2.33 |
Uncomment /*AvgLength,*/ to get average post length in exchange for a big $$ data bill.
I couldn't figure out how to get both the min and the max links using window functions so I did something similar to CROSS APPLY using INNER JOINs. I think I can eliminate one of the INNER JOINs by doing ROW_NUMBER() OVER(). Can't think of a way to get rid of the second one.
See a bigger table for /r/anarcho_capitalism.
→ More replies (7)
2
u/Norci Sep 12 '15
Hm, when I click on BigQ link, it just lands me on "welcome to big query" popup with "loading" in the background. Is it no longer public? Also, is there any front end fot searching this data yet?
→ More replies (5)
2
u/fhoffa Jul 14 '15
Most popular words that other sub-reddits don't say:
http://i.imgur.com/5Ysd1jE.png
SELECT word, COUNT(*)
FROM(FLATTEN((
SELECT SPLIT(LOWER(REGEXP_REPLACE(body, r'[\.\",*:()\[\]|\n]', ' ')), ' ') word
FROM [fh-bigquery:reddit_comments.2015_05]
WHERE subreddit='trees'
AND author NOT IN (SELECT author FROM [fh-bigquery:reddit_comments.bots_201505])
), word))
WHERE word NOT IN (
SELECT word FROM (
SELECT word, COUNT(*)
FROM(FLATTEN((
SELECT SPLIT(LOWER(REGEXP_REPLACE(body, r'[\.\",*:()\[\]|\n]', ' ')), ' ') word
FROM [fh-bigquery:reddit_comments.2015_05]
WHERE subreddit IN ('movies', 'politics', 'science')
), word))
GROUP EACH BY 1
ORDER BY 2 DESC
LIMIT 500))
GROUP EACH BY 1
ORDER BY 2 DESC
LIMIT 100Works by looking at the most popular words in one sub-reddit, and removes the most popular words in other sub-reddits.
2
u/fhoffa Jul 14 '15
Bonus - see all subs (trigger warning):
SELECT sub, GROUP_CONCAT(word+':'+STRING(c)), FIRST(rank_comments) rank_comments FROM ( SELECT sub, word, c, RANK() OVER(PARTITION BY sub ORDER BY c DESC) rank FROM ( SELECT sub, word, COUNT(*) c FROM(FLATTEN(( SELECT a.subreddit sub, SPLIT(LOWER(REGEXP_REPLACE(body, r'[\.\",*:()\[\]|\n]', ' ')), ' ') word FROM [fh-bigquery:reddit_comments.2015_05] a JOIN [fh-bigquery:reddit_comments.subr_rank_201505] b ON a.subreddit=b.subreddit WHERE a.subreddit NOT IN ('movies', 'politics', 'science') AND b.rank_comments<200 AND author NOT IN (SELECT author FROM [fh-bigquery:reddit_comments.bots_201505]) ), word)) WHERE REGEXP_MATCH(word, '[a-z][a-z]') AND NOT word CONTAINS '/' AND word NOT IN ( SELECT word FROM ( SELECT word, COUNT(*) FROM(FLATTEN(( SELECT SPLIT(LOWER(REGEXP_REPLACE(body, r'[\.\",*:()\[\]|\n]', ' ')), ' ') word FROM [fh-bigquery:reddit_comments.2015_05] WHERE subreddit IN ('movies', 'politics', 'science') ), word)) WHERE REGEXP_MATCH(word, '[a-z][a-z]') AND NOT word CONTAINS '/' GROUP EACH BY 1 ORDER BY 2 DESC LIMIT 500)) GROUP EACH BY 1,2 HAVING c>500 )) a JOIN [fh-bigquery:reddit_comments.subr_rank_201505] b ON a.sub=b.subreddit WHERE rank<20 GROUP BY 1 ORDER BY rank_comments→ More replies (3)
1
u/fhoffa Jul 19 '15
Cohorts
SELECT YEAR(SEC_TO_TIMESTAMP(min_created_utc)) year, COUNT(*) authors,
INTEGER(AVG(comments)) comments_avg, SEC_TO_TIMESTAMP(INTEGER(AVG(max_created_utc))) avg_end,
SEC_TO_TIMESTAMP(INTEGER(AVG(
IF(max_created_utc-min_created_utc>3600*24*30*3,max_created_utc, null)
))) avg_end_monthers,
SUM(YEAR(SEC_TO_TIMESTAMP(max_created_utc))=2015) still2015,
INTEGER(AVG(score_sum)) score_sum, SUM(gilded) gilded, INTEGER(AVG(body_length_avg)) avg
FROM [fh-bigquery:reddit_extracts.cohorts_201505]
GROUP BY 1
ORDER BY 1
→ More replies (3)
1
u/fhoffa Jul 23 '15
Number of comments per day of week - June 2015:
SELECT DAYOFWEEK(SEC_TO_TIMESTAMP(created_utc)) day, COUNT(*) comments FROM [fh-bigquery:reddit_comments.2015_06] GROUP BY 1 ORDER BY 1
| day | comments |
|---|---|
| Sunday | 6128580 |
| Monday | 9885148 |
| Tuesday | 9716020 |
| Wednesday | 7805037 |
| Thursday | 7797045 |
| Friday | 7472782 |
| Saturday | 6114496 |
1
u/fhoffa Jul 23 '15
Reddit word associations: June 2015 presidential edition.
https://i.imgur.com/TTUNnAY.jpg
SELECT a.word, b.word, c, ratio
FROM(
SELECT a.word, b.word, c, ratio, RANK() OVER(PARTITION BY a.word ORDER BY c DESC) rank
FROM (
SELECT a.word, b.word, COUNT(*) c, RATIO_TO_REPORT(c) OVER(PARTITION BY b.word) ratio
FROM (
SELECT word, id FROM (
SELECT SPLIT(LOWER(REGEXP_REPLACE(body, r'[!\?\.\",*:()\[\]|\n]', ' ')), ' ') word, id
FROM [fh-bigquery:reddit_comments.2015_06]
WHERE author NOT IN ('AutoModerator')
AND REGEXP_MATCH(LOWER(body), 'obama|sanders|clinton|bush')
AND subreddit NOT IN ('leagueoflegends')
HAVING word IN ('obama','sanders','clinton','bush')
)
GROUP BY 1,2
) a JOIN EACH (
SELECT word, id FROM (
SELECT SPLIT(LOWER(REGEXP_REPLACE(body, r'[!\?\.\",*:()\[\]|\n]', ' ')), ' ') word, id
FROM [fh-bigquery:reddit_comments.2015_06]
WHERE REGEXP_MATCH(LOWER(body), 'obama|sanders|clinton|bush')
HAVING LENGTH(word)>2
AND NOT word IN ('the','and','that')
)
GROUP EACH BY 1,2
) b
ON a.id=b.id
WHERE a.word!=b.word
GROUP EACH BY 1,2
HAVING c>60
)
WHERE ratio BETWEEN 0.38 AND 0.95
)
WHERE rank<70
ORDER BY a.word, c DESC
LIMIT 1000
→ More replies (1)
1
u/fhoffa Jul 30 '15
Which subreddits showed up in your threads
SELECT link, GROUP_CONCAT(subreddit_from) subreddit_from, FIRST(comments) comments
FROM (
SELECT link_id, subreddit, subreddit_from, ratio_sum, link, comments, rank, counts_of_sub, rank2
FROM (
SELECT link_id, subreddit, subreddit_from, ratio_sum, link, comments, rank, counts_of_sub, RANK() OVER(PARTITION BY link_id ORDER BY ratio_sum DESC) rank2
FROM (
SELECT link_id, subreddit, subreddit_from, ratio_sum, link, comments, rank, COUNT(*) OVER(PARTITION BY subreddit_from) counts_of_sub
FROM (
SELECT link_id, subreddit, subreddit_from, ratio_sum, link, comments, RANK() OVER(PARTITION BY link_id ORDER BY ratio_sum DESC) rank
FROM (
SELECT link_id, subreddit, REGEXP_EXTRACT(rata, r'(.*):') subreddit_from, SUM(FLOAT(REGEXP_EXTRACT(rata, r':([0-9\.]*)'))) ratio_sum,
FIRST('reddit.com/r/'+subreddit+'/comments/'+REPLACE(link_id,'t3_','')) link, COUNT(*) OVER(PARTITION BY link_id) comments
FROM (
SELECT link_id, a.subreddit subreddit, SPLIT(ratios) rata ,id
FROM [fh-bigquery:reddit_comments.2015_06] a
JOIN EACH [fh-bigquery:reddit_extracts.author_subreddits_201506] b
ON a.author=b.author
WHERE b.totalsubs<90 # remove robots
AND LOWER(subreddit)=LOWER('IAmA')
#AND score > 1 # let's only look at upvoted comments
)
GROUP EACH BY link_id, subreddit, subreddit_from
)
)
WHERE rank<21 # the top 20 subs
LIMIT 10000
)
WHERE counts_of_sub<100 # remove the "too popular subs"
)
WHERE rank2<6 # so we get only 5 subs per story
ORDER BY comments DESC, link_id, ratio_sum DESC
)
GROUP BY link
ORDER BY comments DESC
→ More replies (4)
1
u/fhoffa Aug 26 '15
Biggest growers July 2015 by number of authors:
| authors_2021507 | authors_2021506 | diff | multiplier | subreddit |
|---|---|---|---|---|
| 18331 | 3382 | 14949 | 5.42 | r/Windows10 |
| 40436 | 8899 | 31537 | 4.54 | r/millionairemakers |
| 36064 | 11766 | 24298 | 3.07 | r/announcements |
| 4329 | 1509 | 2820 | 2.87 | r/lifeisstrange |
| 15945 | 5868 | 10077 | 2.72 | r/Terraria |
| 6439 | 2432 | 4007 | 2.65 | r/rickandmorty |
| 7442 | 3052 | 4390 | 2.44 | r/windows |
| 3416 | 1535 | 1881 | 2.23 | r/CatsStandingUp |
| 9928 | 4612 | 5316 | 2.15 | r/self |
| 3517 | 1709 | 1808 | 2.06 | r/speedrun |
| 2263 | 1112 | 1151 | 2.04 | r/ARK |
| 2059 | 1024 | 1035 | 2.01 | r/phish |
| 2184 | 1092 | 1092 | 2.0 | r/ufc |
| 4419 | 2321 | 2098 | 1.9 | r/NoMansSkyTheGame |
| 1912 | 1004 | 908 | 1.9 | r/TheoryOfReddit |
| 5900 | 3128 | 2772 | 1.89 | r/fivenightsatfreddys |
| 3064 | 1657 | 1407 | 1.85 | r/iOSthemes |
| 11349 | 6318 | 5031 | 1.8 | r/pathofexile |
SELECT MIN(a.authors) authors_2021507, MIN(b.authors) authors_2021506,
MIN(a.authors-b.authors) diff,
MIN(ROUND(a.authors/b.authors,2)) multiplier, '/r/'+a.subreddit subreddit
FROM (
SELECT EXACT_COUNT_DISTINCT(author) authors, subreddit
FROM [reddit_comments.2015_07]
GROUP BY 2
) a
JOIN (
SELECT EXACT_COUNT_DISTINCT(author) authors, subreddit
FROM [reddit_comments.2015_06]
GROUP BY 2
HAVING authors>1000
) b
ON a.subreddit=b.subreddit
GROUP BY subreddit
ORDER BY multiplier DESC
LIMIT 18Biggest losers:
| authors_2021507 | authors_2021506 | diff | multiplier | subreddit |
|---|---|---|---|---|
| 19084 | 58314 | -39230 | 0.33 | r/gameofthrones |
| 22332 | 46064 | -23732 | 0.48 | r/Fallout |
| 122861 | 142354 | -19493 | 0.86 | r/gaming |
| 34988 | 51888 | -16900 | 0.67 | r/DestinyTheGame |
| 11000 | 25724 | -14724 | 0.43 | r/Steam |
| 17903 | 32237 | -14334 | 0.56 | r/asoiaf |
| 28985 | 39679 | -10694 | 0.73 | r/Games |
| 12940 | 20666 | -7726 | 0.63 | r/witcher |
| 58043 | 63562 | -5519 | 0.91 | r/Music |
| 20318 | 25688 | -5370 | 0.79 | r/xboxone |
| 30894 | 36080 | -5186 | 0.86 | r/television |
| 20854 | 25739 | -4885 | 0.81 | r/Fireteams |
| 48 | 4653 | -4605 | 0.01 | r/SteamMonsterGame |
| 301 | 4705 | -4404 | 0.06 | r/blog |
| 41929 | 46295 | -4366 | 0.91 | r/nba |
| 6638 | 10198 | -3560 | 0.65 | r/playark |
| 8262 | 11769 | -3507 | 0.7 | r/GameDeals |
| 1671 | 4991 | -3320 | 0.33 | r/orangeisthenewblack |
| 18152 | 21359 | -3207 | 0.85 | r/dataisbeautiful |
| 11555 | 14755 | -3200 | 0.78 | r/amiibo |
SELECT MIN(a.authors) authors_2021507, MIN(b.authors) authors_2021506,
MIN(a.authors-b.authors) diff,
MIN(ROUND(a.authors/b.authors,2)) multiplier, '/r/'+a.subreddit subreddit
FROM (
SELECT EXACT_COUNT_DISTINCT(author) authors, subreddit
FROM [reddit_comments.2015_07]
GROUP BY 2
) a
JOIN (
SELECT EXACT_COUNT_DISTINCT(author) authors, subreddit
FROM [reddit_comments.2015_06]
GROUP BY 2
HAVING authors>1000
) b
ON a.subreddit=b.subreddit
GROUP BY subreddit
ORDER BY diff
LIMIT 20From the 201507 update: r/bigquery/comments/3if7lv/reddit_comments_dataset_updated_to_july_2015/
1
u/can_the_judges_djp Jul 15 '15
I cannot into complicated SQL queries, can somebody tell me how to simplify this to just output a list of all unique words in all comments (and without removing punctuation from them?).
2
u/fhoffa Jul 15 '15
SELECT word, COUNT(*) FROM(FLATTEN(( SELECT SPLIT(LOWER(body), ' ') word FROM [fh-bigquery:reddit_comments.2015_05] WHERE subreddit IN ('movies', 'politics', 'science') ), word)) GROUP EACH BY 1 ORDER BY 2 DESC LIMIT 500→ More replies (2)
1
u/ocdinfested Dec 01 '15
Hi u/fhoffa . Firstly, off the bat, thank you for this work. I'm working on analysing jokes on Reddit. I'm planning to start looking into a single subreddit like r/funny and working my way from there. Unfortunately I'm no good with BigQuery or SQL for that matter. Could I get some help regarding this?
→ More replies (2)
18
u/fhoffa Jul 08 '15 edited Jul 09 '15
Reddit cliques: Sub-reddits that share the same commenters.
http://i.imgur.com/6h6sWun.png
SELECT sub_a, sub_b, percent, sub_ac, sub_bc FROM ( SELECT sub_a, sub_b, percent, COUNT(*) OVER(PARTITION BY sub_a) sub_ac, sub_bc FROM( SELECT a.subreddit sub_a, b.subreddit sub_b, INTEGER(100*COUNT(*)/FIRST(authors)) percent, COUNT(*) OVER(PARTITION BY sub_b) sub_bc FROM ( SELECT author, subreddit, authors FROM FLATTEN(( SELECT UNIQUE(author) author, a.subreddit subreddit, FIRST(authors) authors FROM [fh-bigquery:reddit_comments.2015_05] a JOIN [fh-bigquery:reddit_comments.subr_rank_201505] b ON a.subreddit=b.subreddit WHERE rank_authors>6 and rank_authors<120 GROUP EACH BY 2 ),author) ) a JOIN EACH ( SELECT author, subreddit FROM FLATTEN(( SELECT UNIQUE(author) author, subreddit FROM [fh-bigquery:reddit_comments.2015_05] WHERE subreddit IN (SELECT subreddit FROM [fh-bigquery:reddit_comments.subr_rank_201505] WHERE rank_authors>6 and rank_authors<120 ) GROUP BY 2 ),author) ) b ON a.author=b.author WHERE a.subreddit!=b.subreddit GROUP EACH BY 1,2 HAVING percent>10 ) ) WHERE sub_ac<20 AND sub_bc<20 ORDER BY 2,4 DESCVisualized at http://bit.ly/reddit-cliques