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
```

#### 1 comment:

1. Your blogs are amazing. Keep sharing. I love them Are you also searching for urgent assignment help? we are the best solution for you. We are best known for delivering the best urgent assignment help.