Game Play Analysis II

easy database sql window function

Problem

Return each player's device_id on their first login date.

Inputrows = [(1,2,'2016-03-01',5),(1,2,'2016-05-02',6),(2,3,'2017-06-25',1)]

Output[(1,2),(2,3)]

One pass keeps (min_date, device); a row matching min_date overwrites with that device.

rows (pid,device,date ; …)

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

Explanation

We want the device each player used on the day they first logged in. The plan has two parts: figure out each player's first login date, then return the device sitting on that exact (player, date) row.

The inner query SELECT player_id, MIN(event_date) ... GROUP BY player_id produces one (player, first-date) pair per player. The outer query then keeps only the Activity rows whose (player_id, event_date) matches one of those pairs.

The WHERE (player_id, event_date) IN (...) is a tuple match: a row survives only if both its player and its date line up with a first-login pair. The device_id from that surviving row is exactly the answer.

Example: player 1's earliest date is 2016-03-01, and that row's device is 2, so we output (1, 2). The later 2016-05-02 row for player 1 does not match the first date and is ignored.

Time: O(n) Space: O(players)