Skip to content

Instantly share code, notes, and snippets.

@napsternxg

napsternxg/HackerNews_GoogleBQ.md

Last active March 12, 2019 00:06
Show Gist options
  • Save napsternxg/73cf926c784d4a1b9799 to your computer and use it in GitHub Desktop.
Save napsternxg/73cf926c784d4a1b9799 to your computer and use it in GitHub Desktop.
Download ZIP
TOP 10 HackerNews posts per year
Raw
HackerNews_GoogleBQ.md

Data collected from Google Big Query:

SELECT id, title, story_score, publish_year, title_rank, FORMAT_UTC_USEC(time_ts) as publish_time
  FROM
  (
    SELECT title, max(score) as story_score, Year(time_ts) as publish_year, time_ts, id, RANK() OVER(PARTITION BY publish_year ORDER BY story_score DESC) as title_rank
      FROM [fh-bigquery:hackernews.stories] WHERE title IS NOT NULL and time_ts is not NULL
      GROUP BY publish_year, title, time_ts, id
   )
HAVING title_rank <= 10
ORDER BY publish_year DESC, title_rank

Download the results as HN_TABLE.txt

Processing done in Pandas:

df_HN = pd.read_csv("HN_TABLE.txt")
df_HN["publish_time"] = pd.to_datetime(df_HN.publish_time)
df_HN["URL_TITLE"] = df_HN.apply(lambda x: "[%s](https://news.ycombinator.com/item?id=%s)" % (x["title"], x["id"]), axis=1)
df_HN[["URL_TITLE", "story_score", "publish_year", "title_rank", "publish_time"]].to_csv("HN_DATA.md", sep="|", index=False)

Data:

title story_score publish_year title_rank publish_time
G is for Google 2228 2015 1 2015-08-10 20:45:29
Email Encryption Software Relies on One Guy, Who Is Going Broke 2088 2015 2 2015-02-05 15:46:51
14-Year-Old Boy Arrested for Bringing Homemade Clock to School 1952 2015 3 2015-09-16 13:00:50
Same-Sex Marriage Is a Right, Supreme Court Rules 1905 2015 4 2015-06-26 14:13:55
YC Research 1775 2015 5 2015-10-07 18:15:25
Microsoft Launches Visual Studio Code, a Free Cross-Platform Code Editor 1608 2015 6 2015-04-29 16:20:57
Leonard Nimoy, Spock of �Star Trek,� Dies at 83 1584 2015 7 2015-02-27 17:12:42
�Two days ago the police came to me and wanted me to stop working on this� 1543 2015 8 2015-08-22 04:12:56
FCC Passes Strict Net Neutrality Regulations on 3-2 Vote 1489 2015 9 2015-02-26 18:01:41
Amazon Web Services in Plain English 1479 2015 10 2015-09-11 06:35:12
Tim Cook Speaks Up 3086 2014 1 2014-10-30 11:12:23
2048 2903 2014 2 2014-03-10 15:44:42
Microsoft takes .NET open source and cross-platform 2376 2014 3 2014-11-12 15:37:27
Today is The Day We Fight Back 2061 2014 4 2014-02-11 08:12:28
Launching in 2015: A Certificate Authority to Encrypt the Entire Web 2019 2014 5 2014-11-18 15:18:36
All Our Patent Are Belong To You 1999 2014 6 2014-06-12 17:02:33
Drop Dropbox 1990 2014 7 2014-04-10 13:56:46
The Heartbleed Bug 1768 2014 8 2014-04-07 19:57:13
Hacker News API 1715 2014 9 2014-10-07 17:38:45
Facebook Fraud [video] 1678 2014 10 2014-02-10 15:26:52
Don't Fly During Ramadan 2744 2013 1 2013-08-22 17:10:57
Hyperloop 2666 2013 2 2013-08-12 20:29:01
Aaron Swartz commits suicide 2103 2013 3 2013-01-12 07:29:13
Google Reader shutting down 1964 2013 4 2013-03-13 23:13:29
A Most Peculiar Test Drive 1859 2013 5 2013-02-14 07:37:14
Lavabit abruptly shuts down 1831 2013 6 2013-08-08 19:12:34
Heroku's Ugly Secret: The story of how the cloud-king turned its back on Rails 1763 2013 7 2013-02-13 21:47:09
Breaking down Amazon's mega dropdown 1734 2013 8 2013-03-06 13:20:58
XKeyscore: NSA program collects 'nearly everything a user does on the internet' 1688 2013 9 2013-07-31 13:05:43
NSA infiltrates links to Yahoo, Google data centers worldwide 1604 2013 10 2013-10-30 16:37:56
Show HN: This up votes itself 3536 2012 1 2012-03-23 00:40:39
Request for Startups: Kill Hollywood. 2060 2012 2 2012-01-20 22:38:02
$1B of TSA Nude Body Scanners Made Worthless By Blog 1637 2012 3 2012-03-06 23:39:16
Light Table - a new IDE concept 1592 2012 4 2012-04-13 14:52:29
Outlawed by Amazon DRM 1445 2012 5 2012-10-22 09:39:35
Show HN: Meteor, a realtime JavaScript framework 1386 2012 6 2012-04-10 22:55:40
"""Lighten up""" 1371 2012 7 2012-03-21 19:15:31
Company withdrawing from Facebook as analytics show 80% of ad clicks from bots 1347 2012 8 2012-07-30 15:21:44
Hit men, click whores, and paid apologists: Welcome to the Silicon Cesspool 1319 2012 9 2012-02-13 23:04:00
Farewell Stack Exchange 1233 2012 10 2012-02-06 23:01:04
Steve Jobs has passed away. 4339 2011 1 2011-10-05 23:42:23
Watch a VC use my name to sell a con 2376 2011 2 2011-11-29 02:23:06
Rob Pike: Dennis Ritchie has died 1958 2011 3 2011-10-13 01:14:23
Boot a linux kernel right inside your browser. 1820 2011 4 2011-05-17 05:09:49
Paul Graham: SOPA Supporting Companies No Longer Allowed At YC Demo Day 1742 2011 5 2011-12-22 20:53:47
A Sister�s Eulogy for Steve Jobs 1736 2011 6 2011-10-30 18:15:52
Steve Jobs Resigns as CEO of Apple 1667 2011 7 2011-08-24 22:37:35
John McCarthy Has Died 1619 2011 8 2011-10-24 19:44:59
I was once a Facebook fool 1390 2011 9 2011-09-23 13:24:09
Stripe: instant payment processing for developers 1249 2011 10 2011-09-29 19:32:22
Introducing Word Lens 1297 2010 1 2010-12-17 00:31:01
Today you, tomorrow me 1270 2010 2 2010-12-14 08:15:59
A new approach to China 1145 2010 3 2010-01-12 23:04:48
So A Blogger Walks Into A Bar� 1098 2010 4 2010-09-21 22:40:57
Google Beatbox 1028 2010 5 2010-11-29 23:47:20
Thoughts on Flash 962 2010 6 2010-04-29 13:26:21
Keep calm & carry on: What you didn't know about the reddit story 776 2010 7 2010-08-03 15:14:14
P ? NP 740 2010 8 2010-08-08 18:42:29
Salesforce Buys Heroku (YC W08) For $212 Million In Cash 717 2010 9 2010-12-08 11:04:31
Firesheep: Easy HTTP session hijacking from within Firefox 714 2010 10 2010-10-25 01:00:47
How I Hacked Hacker News (with arc security advisory) 928 2009 1 2009-06-03 16:27:07
Apple's Mistake 723 2009 2 2009-11-19 18:29:32
A terrible tragedy: Dan Haubert of Ticketstumbler (fallentimes) has passed away 626 2009 3 2009-10-03 20:23:18
Secret copyright treaty leaks. It's bad. Very bad. 588 2009 4 2009-11-04 01:37:45
Microsoft rips off Plurk 581 2009 5 2009-12-14 16:42:59
_why is no more 558 2009 6 2009-08-19 17:06:19
New users: Welcome. Please read the site guidelines. 532 2009 7 2009-01-31 06:36:50
The End Of The CrunchPad 435 2009 8 2009-11-30 16:00:58
"Last.fm: ""Techcrunch are full of shit""" 413 2009 9 2009-02-23 16:29:55
Petabytes on a budget: How to build cheap cloud storage 401 2009 10 2009-09-01 13:51:30
Startup Ideas We'd Like to Fund 532 2008 1 2008-07-19 16:33:35
Why to Start a Startup in a Bad Economy 254 2008 2 2008-10-16 12:08:56
Love, Internet Style 242 2008 3 2008-12-12 04:15:35
Arc's Out 240 2008 4 2008-01-29 21:00:46
You weren't meant to have a boss 239 2008 5 2008-03-20 20:20:06
Wow, check out this guy's comment 233 2008 6 2008-12-29 08:19:46
Congrats to PG on getting hitched 228 2008 7 2008-06-01 15:37:23
�Single?� Lawn Signs Conquer the American Landscape 228 2008 7 2008-11-06 16:17:06
Golden Rules for Making Money by P. T. Barnum (1880) 227 2008 9 2008-09-20 20:29:49
I don't want to work very hard 224 2008 10 2008-12-11 18:45:05
Diary of a Failed Startup 224 2008 10 2008-06-23 15:49:17
How I Turned Down $300,000 from Microsoft to go Full-Time on GitHub 224 2008 10 2008-10-20 20:29:14
Please tell us what features you'd like in news.ycombinator 262 2007 1 2007-02-21 19:22:09
Number of founders - statistics 232 2007 2 2007-11-08 17:23:27
Finally, voting without refresh 176 2007 3 2007-06-12 19:00:13
How Not to Die 169 2007 4 2007-08-30 17:04:50
Why we made this site 166 2007 5 2007-02-20 21:32:37
Hacker News 150 2007 6 2007-08-14 18:41:57
Holding a program in one's head 142 2007 7 2007-08-23 16:40:37
Code's Worst Enemy 125 2007 8 2007-12-19 14:37:03
CMU professor gives his last lesson on life 121 2007 9 2007-09-20 02:37:56
Why to Apply to YCombinator 113 2007 10 2007-10-09 17:05:40
Y Combinator 61 2006 1 2006-10-09 18:21:51
A Student's Guide to Startups 16 2006 2 2006-10-09 18:30:28
Black triangle: a useful shorthand and metaphor 12 2006 3 2006-10-14 22:55:41
Google Acquires YouTube For $1.6B 12 2006 3 2006-10-10 14:04:03
Feld: Question Regarding NDAs 11 2006 5 2006-10-09 19:51:43
LikeBetter featured by BBC 10 2006 6 2006-10-09 19:17:39
MySpace: Not a purely viral start 9 2006 7 2006-10-10 03:54:25
The richest graduate student ever 9 2006 7 2006-10-12 03:52:03
Salaries at VC-backed companies 8 2006 9 2006-10-09 20:00:38
Woz Interview: the early days of Apple 7 2006 10 2006-10-09 18:40:33
Top ten geek business myths 7 2006 10 2006-10-12 22:38:42
NYT: How Friendster Blew It 7 2006 10 2006-10-15 22:48:30
Google, YouTube acquisition announcement could come tonight 7 2006 10 2006-10-09 18:51:04
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment