Select Top Viewed From Last 7 Days

Question

I have a table with a date stamp E.g (1241037505). There's also a column with the number of views.

The data stamp resembles when it was created.

So I want to select the top viewed threads from the past week.

How do I do this?

Answer 1

SELECT * FROM table WHERE Date_Created > (7 days ago value) ORDER BY Hits LIMIT 0,100

or you could use this (per WishCow's Answer)

SELECT * FROM table WHERE Date_Created > SUBDATE(NOW(), '7 day') ORDER BY Hits LIMIT 0,100

Answer 2

SELECT * FROM table WHERE createdon > SUBDATE(NOW(), '7 day') ORDER BY hits DESC;

See: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_subdate

Answer 3

The data you're currently tracking isn't going to allow you to select the top viewed in the last week. It will show you the top viewed over all time, or the most viewed items created in the last week. If something was created two weeks ago, but was viewed more than anything else during the last week you cannot determine that from the data you're tracking. One way I can see to do it would be to track the number of hits each content item gets each day of the week.

create table daily_hits {
    cid integer, -- content id points to the table you already have
    dotw smallint, -- 0-6 or similar
    hits integer
    PRIMARY KEY (cid, dotw)
}

Whenever you increase the hit count on the content item, you would also update the daily_hits table for the given content id and day of the week. You would need a function that converted the current date/time to a day of the week. MySql provides DAYOFWEEK for this purpose.

To get the most viewed in the last week, you could query like this:

SELECT cid, SUM(hits) FROM daily_hits GROUP BY cid ORDER BY SUM(hits) DESC

You will need some type of scheduled job that deletes the current day of the week at midnight so you aren't accumulating forever and essentially performing the same accumulation happening on the hits column of the current table.

Answer 4

Try this:

SELECT * WHERE
DATEDIFF(NOW(),created_date) < 7