Game Play Analysis II

Find the first login device of each visited player.

Example :

Table: Activity

+-----------+-----------+------------+--------------+

| player_id | device_id | event_date | games_played |

+-----------+-----------+------------+--------------+

| 1         | 2         | 2020-12-01 | 5            |

| 1         | 2         | 2021-01-02 | 6            |

| 2         | 3         | 2021-01-04 | 1            |

| 3         | 1         | 2020-12-02 | 8            |

| 3         | 4         | 2020-12-25 | 5            |

+-----------+-----------+------------+--------------+

Result

+-----------+-------------+

| player_id | device_id   |

+-----------+-------------+

| 1         | 2   |

| 2         | 3   |

| 3         | 1  |

+-----------+-------------+

Approach:

Mysql

select distinct player_id, device_id

from Activity

where (player_id, event_date) in (

    select player_id, min(event_date)

    from Activity

    group by player_id);

Oracle

select distinct player_id, device_id

from Activity

where (player_id, event_date) in (

    select player_id, min(event_date)

    from Activity

    group by player_id);