[LeetCode] 512. Game Play Analysis II

https://leetcode.com/problems/game-play-analysis-ii/

Game Play Analysis II - LeetCode

player_id별로 최초 사용한 device_id를 출력한다.

대신 이번에는 GROUP BY를 사용하지 않고, player_id와 최소 event_date를 뽑아서 이와 매치되는 row를 출력해 player_id, device_id를 출력한다.

즉 앞서 풀었던 1단계를 활용하되, 더 나아가야 한다.

 

Algorithm

1. Select a single tuple for each player, namely (player_id, event_date), where event_date is the earliest occurring event_date for the player in question

 

2. Identify all rows in the Activity table whose player_id and event_date values match those in the tuple described above, and select the corresponding player_id and device_id vavlues from these rows.

SELECT
    A1.player_id, 
    A1.device_id
FROM Activity A1
WHERE
    (A1.player_id, A1.event_date) IN (
        SELECT
            A2.player_id,
            MIN(A2.event_date)
        FROM Activity A2
        GROUP BY A2.player_id
    );