Reddit Style Score Degridation Over Time In MySQL

I'm attempting to create a reddit style score degradation system for entries on a system. I've got a MySQL view setup to calculate the total "Score" (sum of all up/down votes). I'm having trouble creating a simple, but effective system for moving entries down the page (so that newer entries end up at the top, but a high score can move entries to the top that would otherwise have aged off)...

Here's the closest bit of SQL I've been able to create thus far:

(SUM(v.Score) - (TIMESTAMPDIFF(MINUTE, t.Genesis, NOW()) *               IF(TIMESTAMPDIFF(MINUTE, t.Genesis, NOW()) > 1440,                     0.1, 0.003)) ) as "Weight", 

v.Score is a 1 or a -1 dependent on user votes. t.Genesis is the timestamp on the entry itself.

Any help or suggestions would be appreciated.

1 Answers

Answers 1

You can implement the same ranking algorithm than Hacker News :

Implementing the Hacker News ranking algorithm in SQL

@OMG Ponies solution:

 SELECT x.*     FROM POSTS x     JOIN (SELECT p.postid,                   SUM(v.vote) AS points             FROM POSTS p             JOIN VOTES v ON 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.timestamp))/3600)+2, 1.5) DESC    LIMIT n 

x.timestamp is your t.Genesis, v.vote is your v.Score

If You Enjoyed This, Take 5 Seconds To Share It

• Facebook
• Twitter
• Google+
• StumbleUpon
• Digg
• Delicious
• LinkedIn
• Reddit
• Technorati