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
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.
ReplyDelete