Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH avg_subreddit AS
- (SELECT avg(score) avg_score,
- subreddit
- FROM `fh-bigquery.reddit_posts.2017_10`
- WHERE subreddit IS NOT NULL
- GROUP BY subreddit),
- max_subredit AS
- (SELECT avg(score) max_score,
- rpo.subreddit
- FROM `fh-bigquery.reddit_posts.2017_10` AS rpo
- JOIN avg_subreddit AS avs ON avs.subreddit=rpo.subreddit
- WHERE rpo.score>avs.avg_score
- GROUP BY rpo.subreddit),
- rank_out AS
- (SELECT score / max_score percent,
- max_score,
- score,
- rpo.subreddit,
- url,
- permalink,
- title,
- RANK() OVER(PARTITION BY rpo.subreddit
- ORDER BY score DESC) AS rank_
- FROM `fh-bigquery.reddit_posts.2017_10` AS rpo
- JOIN max_subredit asu ON asu.subreddit=rpo.subreddit
- WHERE score > 0
- AND score / max_score > 1
- ORDER BY score / max_score DESC)
- SELECT *
- FROM rank_out ro
- WHERE rank_ = 1
- LIMIT 1000
Add Comment
Please, Sign In to add comment