Algorithm To Select Most Popular Places from Database

Question

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:

1. VIEW - View it's profile
2. RATING - Rate it on a scale of 1-5 stars
3. REVIEW - Review it
4. COMPLETED - Mark that they've been there
5. WISH LIST - Mark that they want to go there
6. 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!

Answer 1

Use the lower bound of the Wilson score confidence interval for a Bernoulli parameter!