rank over time periods

[snapbackz]

I was always curious how sites like digg.com have links so you can view popular items over time periods (e.g. today, 24hours, week, month). What is the general idea behind this? Roll up table stats daily and publish them to another stats table with the item_id and score?

[sneakyimp]

they probably attach a date to each vote so they can determine how many votes have happend within a given timeframe. then you can run a query like this:

SELECT COUNT(*) FROM votes WHERE date_submitted >= '2009-01-01' AND date_submitted < CURRENT_DATE()

[snapbackz]

Hi, thanks for the reply but wont that just give me a total count of votes within that time frame? I want to list the items by votes during a time frame is order of highest to lowest....

[sneakyimp]

so you put in a GROUP BY clause. Let's imagine over at Digg that each vote record has a url_id:

SELECT COUNT(*) FROM votes WHERE date_submitted >= '2009-01-01' AND date_submitted <= CURRENT_DATE() GROUP BY url_id

I think that would tell you how many votes each url_id received from 12 AM January 1st through today.