Algorithm To Select Most Popular Places from Database
- by Russell C.
We have a website that contains a database of places. For each place our users are able to take one of the follow actions which we record:
VIEW - View it's profile
RATING - Rate it on a scale of 1-5 stars
REVIEW - Review it
COMPLETED - Mark that they've been there
WISH LIST - Mark that they want to go there
FAVORITE - Mark that it's one of their favorites
In our database table of places each place contains a count of the number of times each action above was taken as well as the average rating given by users.
views
ratings
avg_rating
completed
wishlist
favorite
What we want to be able to do is generate lists of the top places using the above information. Ideally, we would want to be able to generate this list using a relatively simple SQL query without needing to do any legwork to calculate additional fields or stack rank places against one another. That being said, since we only have about 50,000 places we could run a nightly cron job to calculate some fields such as rankings on different categories if it would make a meaningful difference in the overall results of our top places.
I'd appreciate if you could make some suggestions on how we should think about bubbling the best places to the top, which criteria we should weight more heavily, and given that information - suggest what the MySQL query would need to look like in order to select the top 10 places.
One thing to note is that at this time we are less concerned with the recency of a place being popular - meaning that looking at the aggregate information is fine and that more recent data doesn't need to be weighted more heavily.
Thanks in advance for your help & advice!