StackOverflow's Popularity algorithm in MySQL

Question

How would you write SO's Popularity algorithm in MySQL?

The algorithm is detailed here: http://stackoverflow.com/questions/32397/popularity-algorithm.

thanks!

Answer 1

It's relatively simple.

t = (time of entry post) - (Dec 8, 2005)

You would convert the date values to timestamps (you can use unix_timestamp), which gives you an integer that can be used in the rest of the comparisons.

x = upvotes - downvotes

This one should be pretty easy... obviously MySQL supports subtraction.

y = {1 if x > 0, 0 if x = 0, -1 if x < 0)
z = {1 if x < 0, otherwise x}

For these, take a look at MySQL's case statement.

log(z) + (y * t)/45000

MySQL has a log function, so this one should be easy too, just simple math.

And, you tie it all together with a select statement. You can store intermediate calculations in your select statement using user-defined variables. For example:

select @x := (upvotes - downvotes) as x,
       (@x > 4) as isXGreaterThanFour