Store weekly/monthly/alltime data

[jackkicker]

I have an arcade website where each game has a "Daily Plays" column in a SQL database and at the end of every day I add this value to a "All Time Plays" column and reset the "Daily Plays" column. 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?

[Old Pedant]

Just do that in you SQL query.

Since the definition of "last 7 days" and "last 30 days" changes each and every day, it would make no sense at all to have to keep updating records to make them reflect that status. For that matter, I don't understand the point of having "Daily Plays" and "All Time Plays" columns.

If you have a DATETIME or TIMESTAMP column with the date and/or time of the game, that's all you need. If that column is not already indexed, add an index to your table for that column.

Then:

// to get today's games -- equivalent to your Daily Plays:
SELECT list, of, fields FROM table WHERE DATE(gameDateAndTime) = CURDATE();

// to get last week of games, NOT including today:
SELECT list, of, fields FROM table 
WHERE DATE(gameDateAndTime) 
    BETWEEN = DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
    AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) 

// to get last 30 days of games, NOT including today:
SELECT list, of, fields FROM table 
WHERE DATE(gameDateAndTime) 
    BETWEEN = DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
    AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) 

// to get All Time Plays -- all games, NOT including today:
SELECT list, of, fields FROM table 
WHERE DATE(gameDateAndTime) < CURDATE()

Again, if that gameDateAndTime field is indexed, this will be, if anything, *MORE* efficient than your current scheme of adding a column.

The MySQL DATE() function extracts the date-only from a DATETIME or TIMESTAMP value. The CURDATE() function returns, of course, the current date. And DATE_SUB() should be obvious.

[Old Pedant]

And, incidentally, if you don't want to keep coding those queries to get the desired range of dates, just create some VIEWs of your table.

Example:

CREATE VIEW WeeklyPlaysView 
AS
SELECT * FROM yourtable 
WHERE DATE(gameDateAndTime) 
    BETWEEN = DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
    AND DATE_SUB(CURDATE(), INTERVAL 7 DAY)

And now you can just use those views in any other queries. For example:

SELECT playerID, COUNT(*) AS gamesPlayedInWeek
FROM WeeklyPlaysView
GROUP BY playerID
ORDER BY gamesPlayedInWeek DESC
LIMIT 10

will get you the top 10 players (by number of games played) in the last week.

And so on.

[jackkicker]

Thank you for your reply.

But I think my question wasn't clear. What I need to know is what games we're the most popular today, this week (last 7 days), this month (last 30 days) and all time from all visitors together.

[Old Pedant]

LOL! No, that was not AT ALL clear.

So you want this all in a single MySQL query?

All in one record? Or one record per time period?

And when you say "this week", do you mean "including today" or "excluding today"? And ditto for "this month" and "all time"?

Assuming you do mean "including today" and assuming you want all the values in a single record:

SELECT gamename, 
       SUM( IF(DATE(gamePlayDate) = CURDATE(), 1, 0 ) ) AS today,
       SUM( IF(DATE(gamePlayDate) >= DATE_SUB(CURDATE(), INTERVAL 6 DAY), 1, 0 ) ) AS thisweek,
       SUM( IF(DATE(gamePlayDate) >= DATE_SUB(CURDATE(), INTERVAL 29 DAY), 1, 0 ) ) AS thismonth,
       COUNT(*) AS alltime
FROM yourtable
GROUP BY gamename
ORDER BY gamename

*** BUT ***
If you really want *THE* most popular game for each of those periods, then actually my code from Post #3 is what you want!

You would create one VIEW for each of the four periods, in the same vein as the one VIEW I show, and then use a query akin to the second one I show in that post for each of the periods.

There is no really effective way to get the most popular in each of those periods into a single query. Oh, it can be done, using a UNION. But that UNION won't really save you anything in performance over the four separate queries.

[Old Pedant]

Oh, heck. Here's the UNION version. It's not bad. It's just no better than 4 separate queries.

SELECT 'today' AS period, gamename, COUNT(*) AS plays
FROM table WHERE DATE(gameDateAndTime) = CURDATE()
GROUP BY period,gamename ORDER BY plays DESC LIMIT 1
    UNION
SELECT 'week' AS period, gamename, COUNT(*) AS plays
WHERE DATE(gameDateAndTime) >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) 
GROUP BY period,gamename ORDER BY plays DESC LIMIT 1
    UNION
SELECT 'month' AS period, gamename, COUNT(*) AS plays
WHERE DATE(gameDateAndTime) >= DATE_SUB(CURDATE(), INTERVAL 29 DAY) 
GROUP BY period,gamename ORDER BY plays DESC LIMIT 1
    UNION
SELECT 'alltime' AS period, gamename, COUNT(*) AS plays
GROUP BY period,gamename ORDER BY plays DESC LIMIT 1

That gets 1 record per "period" (today, week, month, alltime) that shows the most popular gamename for the period.

Again, the numbers *ALL* include today's games.

[jackkicker]

Thanks again for the reply!

I'm sorry but since my English isn't very good I think you still didn't understand what I need.. I'll try stating it differently so you better understand.

For example, for the game "Age of War" I would need to record that the game was played:

- 55 times today
- 354 times last 7 days
- 1040 times last 30 days
- 12891 times total since the game was added.

And I would need to record that info for every single games. This way I would be able to sort the games to see which ones were most popular today, last 7 days, last 30 days and all time.

So my question is what is the best and most efficient way to record that info into my SQL table? As of now I only record how many times a game was played by incrementing a counter every time a game is played, without using any timestamp.

I guess I would need at least 30 columns (one for each of the last 30 days) + 1 column for all time plays?

[Old Pedant]

OH NO! Terrible DB design!

You simply want ONE RECORD for EACH TIME a game is played!

For example:

CREATE TABLE games (
    gameID INT AUTO_INCREMENT PRIMARY KEY,
    gameName VARCHAR(100)
) ENGINE INNODB;

CREATE TABLE players (
    playerID INT AUTO_INCREMENT PRIMARY KEY,
    playerName VARCHAR(100),
    password VARCHAR(30)
    ...
) ENGINE INNODB;

CREATE TABLE gamesPlayed (
    playerID INT,
    gameID INT,
    whenPlayed TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FOREIGN KEY playerID REFERENCES players(playerID),
    CONSTRAINT FOREIGN KEY gameID REFERENCES players(gameID)
) ENGINE INNODB;

See it? You record each and every game, who played it, what game they played, and when they played it.

*THEN* you can use my answer, above, to find the most popular game in any period.

[Old Pedant]

I don't really recommend this alternative, but you *COULD* do something very similar to what you are now doing. Like this:

CREATE TABLE games (
    gameID INT AUTO_INCREMENT PRIMARY KEY,
    gameName VARCHAR(100)
) ENGINE INNODB;

CREATE TABLE dailyGames (
    gameID INT,
    datePlayed DATE,
    playCount INT,
    PRIMARY KEY ( gameID, datePlayed )
    CONSTRAINT FOREIGN KEY gameID REFERENCES players(gameID)
) ENGINE INNODB;

So now, each time a game is played, you do:

INSERT INTO dailyGames ( gameID, datePlayed, playCount )
VALUES( 33, '2013-1-15', 1 )
ON DUPLICATE KEY playCount = playCount + 1

If this is the first time game #33 is played *TODAY*, that will add a new record for that game for today. If it is not the first time, it will bump the playCount by 1.

Notice that this means you will have one record PER GAME PER DAY.

And now what you are after is easy.

For example, to get the 20 most popular games in the last 30 days:

SELECT G.gamename, D.gameid, SUM(playCount) AS totalPlays
FROM games AS G, dailyGames AS D
WHERE G.gameid = D.gameid
AND D.gameDate >= DATE_SUB( CURDATE(), INTERVAL 29 DAY )
GROUP BY G.gamename, D.gameid
ORDER BY totalPlays DESC
LIMIT 20

Stop thinking of one record per game. That's a very very bad idea.

AT THE MINIMUM, do as I show here: One record PER GAME PER DAY.

My own preference would be for the complete audit trail of one record for each and every game played, by game, date, and player. But the scenario I show in this post is a reasonable second choice.

[jackkicker]

Would you recommend your methods even if I have over 1500 games on my site with around 50 plays per day for each game? I get the impression that your method would create too many records and slow the database in the long run.. Don't you think?

[Old Pedant]

So 75,000 records added, per day? Say 75,000,000 records in 3 years?

Well, MySQL could certainly handle that with no problem.

But if you don't really care about tracking games played *per player*, then okay, use my seond idea. That's only 1500 records added per day. So only 1.5 million in 3 years. That's not many, at all, to MySQL. Again, make sure you have the proper primary key:

PRIMARY KEY ( gameID, datePlayed )

Or it might be better to reverse that, come to think of it:

PRIMARY KEY ( datePlayed, gameID )