Get overachieving posts

posts.sql

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