Store weekly/monthly/alltime data using MySQL

I have an arcade website where each game has a "Daily Plays" variable and then at the end of every day I add this value to a "All Time Plays" variable and reset the "Daily Plays" variable. I would now like to record the "Weekly Plays" (last 7 days) and "Monthly Plays" (last 30 days) for those games. What would be the best and most efficient way to do that?

 

Add the "All Time Plays" that are within the past week to the "Weekly Plays" table, delete the ones in "Weekly Plays" that are no longer within the past week, and do the same thing with monthly plays.

 

I don't see how this is possible with his current data structure. A single number for each plays count for each game isn't sufficient. Here's an example:


Game A
Daily: 100
Weekly: 560
Monthly: 3150
All-time: 15300

At the end of the day, when he wants to tabulate new data, he knows that his weekly stats need to get today's 100 plays added...but that also involves dropping off the daily play count of the day 7 days ago...which isn't recorded. The same issue exists for monthly data.

As I see it, you have two solutions, one simpler but less flexible, and one more complex but that will support any kind of statistics you might need in the future. The simple solution involves tracking daily plays data as far back as 31 days. So when running daily tabulations, you would add that 100 plays for today, but drop the plays count for the day last week, and have a new weeklys play count. Follow the same procedure for the monthly stats. It minimizes the data storage required and gives you what you need.

The more complex solution is to start logging plays on an individual basis for every game in a database. Use a small linker table with fields like (`play_id`,`game_id`,`user_id`,`datetime`), and index/foreign key as appropriate. Depending on the site you're running, it could consume a huge amount of data, but it will support any type of statistics you could ever want to use. You could look at historic play data for a game month-by-month over its entire history, or yearly data (2012,2013), or calculate most played games over the last year (as in 12 months period). No matter what you want, if you index the table well, you should be able to calculate any kind of numbers you want, giving you a lot of flexibility. You also would probably be able to eliminate the need to tabulate daily plays numbers every night.