Getting a list of the most popular gems used in public gem projects on github: https://bigquery.cloud.google.com/dataset/bigquery-public-data:github_repos

bigquery-most-popular-gems.sql

SELECT
  gem,
  COUNT(*) c
FROM (
  SELECT
    REGEXP_EXTRACT(
      line,
      r'.*dependency\s*[\'"]([^\'"]*)[\'"]'
    ) gem,
    id
  FROM (
    SELECT
      SPLIT(content, '\n') line,
      id
    FROM [bigquery-public-data:github_repos.sample_contents]
    WHERE
      REGEXP_MATCH(
        content,
        r'.*dependency\s*[\'"][^\'"]*[\'"]'
      ) AND
      sample_path LIKE '%.gemspec'
  )
  GROUP BY gem, id
)
GROUP BY 1
ORDER BY c DESC
LIMIT 500;

I'm thinking there might be something wrong with the splitting part of this query as those numbers seem much lower than I would expect.