How was 2017 for you? Have you learned Rust? Have you invested in Bitcoin? Was it the year of Linux desktop (again)?
In this post, I’d like to see how was the last year for fellow hackers, through Hacker News’ public dataset on Google BigQuery.
Google BigQuery has interesting public datasets and Hacker News is one of them. Deedy Das already did a lot of interesting analysis in 2015, that covered 9 years from 2006. Most of the SQL queries below are taken from his post.
Technically people can upvote/downvote last year’s stories on Hacker News. There is no “archive” mode like Reddit as far as I know. If you run the queries again, the numbers would be a bit different.
Top Stories
Let’s start from the simplest one. What was the most upvoted stories in 2017?
I mainly used Google Cloud Datalab to get the numbers. %%bq query
lets Datalab (which is basically Jupyter Notebook) interrepts a cell as a SQL on BigQuery. If you use BigQuery’s console, you may need #standardSQL
instead.
%%bq query
SELECT title, url, score, id, timestamp FROM `bigquery-public-data.hacker_news.full`
WHERE type = 'story' AND extract(year FROM timestamp) = 2017
ORDER BY score DESC LIMIT 30
Here is the result. Apparently, it was the year of Silent Breakers, even for Hacker News.