I keep track of how many "votes" and "similar adds" each item got. Similar adds are defined as two users adding the same "link".

Here is part of the algorithm (essentially the most important):

   y = day number 
   sy = number of adds on day y

   ∑ y[1:10]  sy / y

However, I'm not sure how I would go about implementing this with sql.

The only way I can think of implementing this is by calculating the number of adds for the past 10 days for each item submitted, which will take forever. (so a sql command with a group by date, where date >= current_date - 9 and then a sum of that).

Even if I keep a table that I update once a day (and run the above sql in the background), that will still be ridiculously slow once the database gets large. Plus the rating will be "outdated" since it's not live (e.g. breaking news "items" will never reach the top).

Does anyone have any experience of how to go about doing this?

Thanks.

(I posted on stack overflow first, but reposted here since thought you guys might have more experience with this topic... http://stackoverflow.com/questions/368369/how-to-implement-a-custom-rating-system-with-a-declining-date-relevance )