areeves87

WITH active_subreddits AS (SELECT subreddit, COUNT(subreddit) AS comments 
                           FROM `fh-bigquery.reddit_comments.2018_06` 
                           GROUP BY subreddit 
                           HAVING comments > 1000),
     p90_threshold     AS (SELECT subreddit, score,
                            CASE WHEN body = '[removed]' THEN 1 ELSE 0 END AS removed,
                            PERCENTILE_DISC(score, 0.9) OVER(PARTITION BY subreddit) AS percentile_90
                           FROM `fh-bigquery.reddit_comments.2018_06`),
     events            AS (SELECT subreddit, removed, percentile_90,
                            CASE WHEN score > 1 THEN 1 ELSE 0 END AS up,