Skip to content

Instantly share code, notes, and snippets.

@vjk2005

vjk2005/hn_books_bigquery.sql

Created January 20, 2018 06:26
Show Gist options
  • Save vjk2005/e668ca1a4884ddb942629ebb7f49444b to your computer and use it in GitHub Desktop.
Save vjk2005/e668ca1a4884ddb942629ebb7f49444b to your computer and use it in GitHub Desktop.
Download ZIP
SQL for finding all book mentions on Hacker News using Google BigQuery
Raw
hn_books_bigquery.sql
SELECT
*
FROM
[bigquery-public-data:hacker_news.full]
WHERE
type="comment" AND (REGEXP_MATCH(text,r'.*amazon.*\/\d{9}.*') OR (text like '% am reading %' OR text like '% was reading %' OR text like '% just finished %' OR text like '% finished reading %' OR text like '% just read %' OR text like '% going to read %' OR text like '% about to read %'))
LIMIT
50000
// dataset and query console: https://bigquery.cloud.google.com/results/netmap-976:bquijob_5a50d154_16109c38bea
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment