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

0 comments:

Blogroll

Follow this blog by Email

Popular Posts