potatosoup potatosoup - 1 year ago 80
MySQL Question

How to query first month of data for each user

Problem Description

I have a data table

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


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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download