I want to implement a ranking system on a website I've been working on and have decided to go with the Hacker News algorithm. My reasoning for choosing this algorithm is simply because it's been described here.
I was looking at this Python code (the language I'm using to build my site) and couldn't figure out how I would implement it.
def calculate_score(votes, item_hour_age, gravity=1.8):
return (votes - 1) / pow((item_hour_age+2), gravity)
id | title | time_submitted
id | postid | userid | score
You can use the data you need in the
ORDER BY clause.
SELECT p.id, p.title, p.time_submitted, SUM(v.score) as num_votes FROM posts p, votes v WHERE v.postid = p.id GROUP BY p.id ORDER BY (SUM(v.score) - 1) / POW(TIMESTAMPDIFF(HOUR,p.time_submitted,NOW()) + INTERVAL 2 HOUR, 1.8) DESC LIMIT 100