General Question

philo23's avatar

Working out top rated items?

Asked by philo23 (193points) February 18th, 2009

Say for example you have a online store where customers rate the items on a scale of 1 to 5, 5 being the best, you can work out an average rating for each item using each rating added together divided by the total amount of ratings.

But how do you work out the top rated items, eg, the top 10 rated items, taking say, only the last two weeks worth of rating into account?

Observing members: 0 Composing members: 0

5 Answers

dynamicduo's avatar

When it comes to programming quandaries like this, I often go back to paper. Rip up a piece of paper and write some demo ratings on it for 5 products, including the database fields and their values. Physically do the activity of calculating the top 3 rated items, and carefully note the algorithm your hands and brain use to do this. Then try to replicate this algorithm in code.

This assumes you have a datestamp in the table with the ratings, without such a field I don’t think you can accomplish your task.

I would do this in a loop which was set to loop for the number of unique products in your online store. Create an array (with 10 spots) outside of the loop to store the results, and one more to store the name of the product (you could use a multidimensional array maybe). Make a query that returns the last 2 weeks worth of ratings for a single product. Calculate its rating. Load the external array and compare the new rating to see if the new rating is higher than anything in the array. If it is, put it into the array in the proper sorted position, and do the same for the name of the product. Once the loop is complete, you are left with two arrays: one of product names and one with the two-week average rating, where the rows correspond to each other.

This is just one approach to how your problem could be solved, likely not the most efficient, but it should work.

fireside's avatar

You basically need to calculate the average rating for each product, based on the last two weeks. Then sort those ratings by date and display the top ten.

Now translate that spreadsheet language into code.
Sorry if this doesn’t help at all

philo23's avatar

Both of your replys help, how ever i cant seem to think of a good efficient way to do it.

dynamicduo's avatar

Then don’t make it efficient. Get it working inefficiently first. I won’t be providing any functional code.

cwilbur's avatar

If you’ve designed your database well, you can do this in a single SQL query without really even getting too complicated.

If you’ve designed your database poorly, you’re out of luck.

Answer this question




to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
Knowledge Networking @ Fluther