MYSQL - Selecting a specific date range to get "current" popular screensavers.

Question

Let's say I have a screensaver website. I want to display the CURRENT top 100 screensavers on the front page of the website.

What I mean is, "RECENT" top 100 screensavers. What would be an example query to do this?

My current one is:

SELECT * FROM tbl_screensavers WHERE WEEK(tbl_screensavers.DateAdded) = WEEK('".date("Y-m-d H:i:s",strtotime("-1 week"))."') ORDER BY tbl_screensavers.ViewsCount, tbl_screensavers.DateAdded

This will select the most viewed ("tbl_screensavers.ViewsCount") screensavers that were added ("tbl_screensavers.DateAdded") in the last week.

However, in some cases there are no screensavers, or less than 100 screensavers, submitted in that week.

So, how can I perform a query which would select "RECENT" top 100 screensavers? Hopefully you have an idea of what I'm try to accomplish when I say "RECENT" or "CURRENT" top screensavers. -- aka. the most viewed, recently - not the most viewed, all-time.

Answer 1

Given no other algorithm to weigh the value of a view vs. a recent view, you would just simply want

SELECT * FROM tbl_screensavers ORDER BY ViewsCount limit 100

However, to capture the concept of "recent" you may want to introduce an algorithm to weigh the recent-ness of a particular view. One way to do that is to assign a daysOld score to each view and show the 100 with the lowest score (with this mechanism, low score is good like in golf).

I'm not enough of a MySQL guru to write the query for that, but it would involve summing up the score, computed based on daysOld=today-dateOfScore and then ordering the result set based on that score, with a limit of 100.