4 ms·

A reminder that all Hacker News posts and comments are available on BigQuery and can be queried for free: https://console.cloud.google.com/marketplace/details/y

A reminder that all Hacker News posts and comments are available on BigQuery and can be queried for free: https://console.cloud.google.com/marketplace/details/y-combinator/hacker-news https://console.cloud.google.com/marketplace/details/y-combi... (the `full` table is up-to-date; ignore the others) Here's a query for a rough reproduction of what's asked in the title: WITH whoishiring_threads AS ( SELECT id FROM `bigquery-public-data.hacker_news.full` WHERE `by` = "whoishiring" AND REGEXP_CONTAINS(title, "Ask HN: Who is hiring?") ) SELECT FORMAT_TIMESTAMP("%Y-%m", `timestamp`) as year_month, COUNT(*) as num_toplevel_posts FROM `bigquery-public-data.hacker_news.full` WHERE parent IN (SELECT id FROM whoishiring_threads) GROUP BY 1 ORDER BY 1 Which results in something like this: https://docs.google.com/spreadsheets/d/13yGlJzFpVzZ-WNHAOsdoOZdo5H2cY_VyiSMXeFSQfBo/edit?usp=sharing https://docs.google.com/spreadsheets/d/13yGlJzFpVzZ-WNHAOsdo... Still a bit of room to clean up the query, though, and there are some differences from the chart in the post.

psnehanshu 4y ago
Google is infamous for breaking backward compatibility, and sunsetting things, so I will not advice anyone to use this to build any products. However it is fine for one-off tasks, like fetching data for this blog post.
pm90 4y ago
I just want to say, in a comment that furthers the discussion in no way, that having something like this available for free is just incredible.
tedmiston 4y ago
I suppose that massive spike is the beginning of the pandemic. It's interesting to see March 2020 (the highest month of all time) and April 2020 (the lowest month since early 2016) back to back.
deleted 4y ago
[deleted]
hrpnk 4y ago
Thanks for formatting the timestamp, which I missed in the original post.
tobr 4y ago
> all Hacker News posts and comments are available on BigQuery and can be queried for free Good thing there aren’t any laws that regulate copyright or privacy, so datasets like this can be published without asking users for consent.
- skrebbel 4y ago
  Anybody could crawl HN and build up the exact same dataset. You choose to publish your words for the world to consume when you submit a comment.
  - tobr 4y ago
    It’s not less illegal because anyone could do it. I have copyright to my comments, and many of them can be considered personal data, especially if they are connected to my username. My rights are not waived because I publish something publicly.
    - dereg 4y ago
      Puzzling comment. Perhaps you should read the Terms of Service. User Content Transmitted Through the Site: With respect to the content or other materials you upload through the Site or share with other users or recipients (collectively, “User Content”), you represent and warrant that you own all right, title and interest in and to such User Content, including, without limitation, all copyrights and rights of publicity contained therein. By uploading any User Content you hereby grant and will grant Y Combinator and its affiliated companies a nonexclusive, worldwide, royalty free, fully paid up, transferable, sublicensable, perpetual, irrevocable license to copy, display, upload, perform, distribute, store, modify and otherwise use your User Content for any Y Combinator-related purpose in any form, medium or technology now known or later developed. However, please review the Applications Privacy Policy located at https://www.ycombinator.com/apply/privacy, for more information on how we treat information included in applications submitted to us. https://www.ycombinator.com/legal/ https://www.ycombinator.com/legal/
      - 4y ago
colmanhumphrey 4y ago
Hey this is nice! I created a little app here that's quite similar but can be run without knowing SQL: https://slight.run/apps/colman/hacker_news_monthly_top_level_comments_by_user https://slight.run/apps/colman/hacker_news_monthly_top_level..., and a graph to go with it: https://slight.run/graphs/colman/hacker_news_who_is_hiring_comments_over_time https://slight.run/graphs/colman/hacker_news_who_is_hiring_c... One thing that's interesting: this is a sneaky case where the line hides two missing months in 2015.

psnehanshu 4y ago

Google is infamous for breaking backward compatibility, and sunsetting things, so I will not advice anyone to use this to build any products. However it is fine for one-off tasks, like fetching data for this blog post.

pm90 4y ago

I just want to say, in a comment that furthers the discussion in no way, that having something like this available for free is just incredible.

tedmiston 4y ago

I suppose that massive spike is the beginning of the pandemic. It's interesting to see March 2020 (the highest month of all time) and April 2020 (the lowest month since early 2016) back to back.

deleted 4y ago

[deleted]

hrpnk 4y ago

Thanks for formatting the timestamp, which I missed in the original post.

tobr 4y ago

> all Hacker News posts and comments are available on BigQuery and can be queried for free Good thing there aren’t any laws that regulate copyright or privacy, so datasets like this can be published without asking users for consent.

skrebbel 4y ago
Anybody could crawl HN and build up the exact same dataset. You choose to publish your words for the world to consume when you submit a comment.
- tobr 4y ago
  It’s not less illegal because anyone could do it. I have copyright to my comments, and many of them can be considered personal data, especially if they are connected to my username. My rights are not waived because I publish something publicly.
  - dereg 4y ago
    Puzzling comment. Perhaps you should read the Terms of Service. User Content Transmitted Through the Site: With respect to the content or other materials you upload through the Site or share with other users or recipients (collectively, “User Content”), you represent and warrant that you own all right, title and interest in and to such User Content, including, without limitation, all copyrights and rights of publicity contained therein. By uploading any User Content you hereby grant and will grant Y Combinator and its affiliated companies a nonexclusive, worldwide, royalty free, fully paid up, transferable, sublicensable, perpetual, irrevocable license to copy, display, upload, perform, distribute, store, modify and otherwise use your User Content for any Y Combinator-related purpose in any form, medium or technology now known or later developed. However, please review the Applications Privacy Policy located at https://www.ycombinator.com/apply/privacy, for more information on how we treat information included in applications submitted to us. https://www.ycombinator.com/legal/ https://www.ycombinator.com/legal/
    - 4y ago

colmanhumphrey 4y ago

Hey this is nice! I created a little app here that's quite similar but can be run without knowing SQL: https://slight.run/apps/colman/hacker_news_monthly_top_level_comments_by_user https://slight.run/apps/colman/hacker_news_monthly_top_level..., and a graph to go with it: https://slight.run/graphs/colman/hacker_news_who_is_hiring_comments_over_time https://slight.run/graphs/colman/hacker_news_who_is_hiring_c... One thing that's interesting: this is a sneaky case where the line hides two missing months in 2015.