BQ Joshi Public datasets sample queries

bigquery-per-github-repo.sql

SELECT repo_name, SUM(ARRAY_LENGTH(REGEXP_EXTRACT_ALL(content, r"(?i)bigquery"))) AS count
  FROM bigquery-public-data.github_repos.contents
  JOIN bigquery-public-data.github_repos.files
    ON contents.id = files.id
  GROUP BY repo_name
  ORDER BY count DESC
  LIMIT 100

github-email-orgs.sql

SELECT email_org, COUNT(email_org) AS ncommits
  FROM (
    SELECT ARRAY_REVERSE(SPLIT(author.email, "@"))[SAFE_OFFSET(0)] AS email_org
      FROM bigquery-public-data.github_repos.commits
    ) AS t1
  GROUP BY email_org
  ORDER BY ncommits DESC

github-google.sql

SELECT SUM(ARRAY_LENGTH(REGEXP_EXTRACT_ALL(content, r"Google")))
  FROM bigquery-public-data.github_repos.contents

space-tabs

https://bit.ly/spacetabs

wikipedia-popular-ja-pages.sql

SELECT title, day, rank, total_views
  FROM (
    SELECT title, day, RANK() OVER (PARTITION BY DAY ORDER BY total_views DESC) AS rank, total_views
      FROM (
        SELECT title, EXTRACT(DAY FROM datehour AT TIME ZONE "Asia/Tokyo") AS day, SUM(views) AS total_views
          FROM `bigquery-public-data.wikipedia.wikidata`
            JOIN `bigquery-public-data.wikipedia.pageviews_2020`
            ON title = ja_wiki
          WHERE datehour >= TIMESTAMP("2020-06-01", "Asia/Tokyo")
            AND datehour < TIMESTAMP("2020-07-01", "Asia/Tokyo")
            AND title != "メインページ"
            AND REGEXP_CONTAINS(wiki, r"^ja")
          GROUP BY title, DAY
      ) AS daily_pageviews
  )
  WHERE rank <= 10
  ORDER BY day, total_views DESC
  LIMIT 1000