BigQuery for most popular countries on Hacker News

gistfile1.txt

SELECT
  b.country,
  SUM(score) as total_score,
  SUM(a.c) as mention_count,
  ROUND(SUM(score) / SUM(a.c), 2) as avg_score
FROM
(
  SELECT
    a.word,
    SUM(a.score) AS score,
    COUNT(1) AS c
  FROM (
    SELECT
      SPLIT(REGEXP_REPLACE(title, '[^a-zA-Z ]', ''), ' ') AS word,
      score
    FROM
      [fh-bigquery:hackernews.stories]) a
  GROUP BY
    a.word
) a
JOIN
  [countries.country] b
ON
  a.word = b.aka
GROUP BY b.country
HAVING mention_count >= 100
ORDER BY avg_score DESC
LIMIT 100