Game Play Analysis II

To report the device that was first logged in for each player, you can join the Activity table with itself on the player_id column and apply conditions to filter the required results. Here’s the query:

1
2
3
4
5
6
7
8

SELECT a.player_id, a.device_id
FROM Activity a
JOIN (
  SELECT player_id, MIN(event_date) AS first_login
  FROM Activity
  GROUP BY player_id
) b
ON a.player_id = b.player_id AND a.event_date = b.first_login;

Explanation:

• The subquery in the JOIN operation calculates the first login date for each player by using the MIN function, grouping the results by player_id.
• Then, the main query joins this subquery result with the original Activity table on both the player_id and the first_login date. This ensures that the result includes the device_id corresponding to the first login date for each player.