As per Paul Graham Explanation of Hacker New Algorithm The algorithm gives much importance to latest articles. So hackers must submit their articles in appropriate time. Number of votes in first one hour will decide fate of the article.

Hacker News Algorithm

f(p, t) =  (p - 1) / (t + 2)^1.5
p = Points
t = Age in Hours
As shown in below diagram we need at least 2 tables to implement  this algorithm.
HN_POST =  Contains Post ID, Age in Time Stamp
HN_VOTE = Contains Post ID, User ID, Votes,   Age of vote in Time Stamp

Create HN_POST table

```create table HN_POST (
postid varchar(20),
ctime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
primary key(postid)
)
```
postid = Unique ID of the post
ctime = Age of the post in TIMESTAMP

Create HN_VOTE table

```create table HN_VOTE (
postid varchar(20),
user varchar(20),
vote int,
ctime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
primary key(postid, user)
)
```
postid = Unique ID of the post
ctime = Age of the vote in TIMESTAMP
vote = Vote by user
user = User ID

SQL Query to select top 20 posts

The below SQL query selects top hot 20 posts
```SELECT x.*
FROM HN_POST x
JOIN (SELECT p.postid,
SUM(v.vote) AS points
FROM HN_POST p, HN_VOTE v where v.postid = p.postid
GROUP BY p.postid) y ON y.postid = x.postid
ORDER BY (y.points - 1)/POW(((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(x.ctime))/3600)+2, 1.5) DESC
LIMIT 0,20
``` Srinivas Dasari find me on facebook follow me on twitter