potatosoup potatosoup - 4 months ago 16
MySQL Question

How to query first month of data for each user

Problem Description

I have a data table

multiplayergame
where there are multiple records for each
UserID
detailing their activities during that multiplayer game, and the date
DateID
that the game occurred on.

Question

How can I query the games for each user that occur within the first month of their first game ? i.e., Grab all the rows for that user that occur within 30 days of the first record for their first multiplayer game?

Answer

This will be slow if you have a lot of users, but it will work. The subquery gets a list of UserID's and their first game's DateID, then selects all the games where the DateID is between this date and the next 30 days (for each user). If you added the first game date to the user table or user the user created_at date you could speed it up a lot because of the full use of indexes.

SELECT * FROM multiplayergame m1 
JOIN (SELECT MIN(m.DateID) as first_game, m.UserID
FROM multiplayergame m
GROUP BY m.UserID) der on der.UserID=m1.UserID and m1.DateID BETWEEN der.first_game AND der.first_game + INTERVAL 30 DAY