I have a MySQL database table that looks like below:
+----------+------------+---------------------+
| name | event | created |
+----------+------------+---------------------+
| Player1 | Logged in | 2023-02-14 10:05:00 |
| Player2 | Logged in | 2023-02-14 10:05:30 |
| Player3 | Logged out | 2023-02-14 10:06:00 |
| Player1 | Logged out | 2023-02-14 10:10:30 |
| Player4 | Logged in | 2023-02-14 10:10:45 |
| Player2 | Logged out | 2023-02-14 10:20:00 |
| Player4 | Logged out | 2023-02-14 10:30:00 |
| Player5 | Logged in | 2023-02-14 10:30:05 |
| Player1 | Logged in | 2023-02-14 10:30:10 |
| Player5 | Logged out | 2023-02-14 10:32:00 |
+----------+------------+---------------------+
What I want to do, is to figure which players might be played by the same person.
To do that, I can look at their respective "Logged in" / "Logged out" events and use that as a pattern.
If a player logs out from the game, and another player logs in within 30 seconds – and this happens a few times – then I can assume they are being played by the same person. Similarily if a player logs in and another one logs out.
In the example above, we can see that:
(row 4) **Player1** -> **Logged out**
(row 5) **Player4** -> **Logged in**
These events took place less than 30 seconds apart.
And again, the same thing happened here:
(row 7) **Player4** -> **Logged out**
(row 9) **Player1** -> **Logged in**
These events took place less than 30 seconds apart as well.
We can therefore assume that Player1 and Player4 are being played by the same person.
What I want to generate as result is a new table, that allows me to search for a specific player.
In this case, I want to search for "Player1" and it should return a list of all players that logged in/logged out within 30 seconds after Player1, and had at least 2 such occurences.
For instance, a table like below would be sufficient as results if I make a search for "Player1":
+----------+------------+
| name | occurences |
+----------+------------+
| Player4 | 2 |
+----------+------------+
Any clue on how I can achieve this?
2
Answers
Something like:
where:
@some_range_start, @some_range_end – dates range to be investigated (remove if you want to investigate the whole table);
@some_limit – minimal amount of such incidents for definite logins pair.
DEMO fiddle
Remember – this query won’t be fast..
PS. The amount of second can be parametrized too, of course.
You can make a query, which uses a subquery to get all created for player 1 and then uses
CROSS JOIN
to compare it to all dates for other playersfiddle