I'm stuck into an algorithm that I'm working on a few days. It's something like this:
I have lots of posts, and people may like or dislike them. In a scale from 0 to 100, the algorithm shows the most liked posts first. But when new posts arrives, they haven't any score yet, so they get to the end of this ranking. What I did: when a post haven't any vote, I put an default score (for example, 75).
When the first user likes this new post, it get the total score (100), but when the user dislike it, it goes to the end of the list (score 0).
What can I do to achieve this ranking for liked posts based on the total number of users who liked it?
If I wasn't clear enough, please tell me
Any help will be appreciated.
What I had done so far:
select id,(
(select cast(count(1) as float) from posts p1 where p1.id = p.id and liked = 1) /
(select cast(count(1) as float) from posts p2 where p2.id = p.id)
)*100 AS value
from posts p
group by id
thanks for the help, but I had solved my problem this way:
I maintained the original query with an additional clause
select id,(
(select cast(count(1) as float) from posts p1 where p1.id = p.id and liked = 1) /
(select cast(count(1) as float) from posts p2 where p2.id = p.id)
)*100 AS value,
(select count(1) from posts p3 where p3.id = p.id) as qty
from posts p
where qty > 5
group by id
So, if a new post comes in, it will have the default value assigned until the fifth user rate it. If the content is truly bad, it goes to the end of the list, otherwise it will stay on top until other users rate it down.
May not be the perfect solution but worked for me