Game Play Analysis I
Problem
Activity(player_id, device_id, event_date, games_played). Return the first login date per player.
Input
rows = [(1,2,'2016-03-01',5),(1,2,'2016-05-02',6),(2,3,'2017-06-25',1),(3,1,'2016-03-02',0),(3,4,'2018-07-03',5)]Output
[(1,'2016-03-01'),(2,'2017-06-25'),(3,'2016-03-02')]One pass keeping a hash map player_id → min(event_date).
SELECT player_id, MIN(event_date) AS first_login
FROM Activity
GROUP BY player_id;Explanation
We want each player's first login date. Since a player can appear in many rows, we just need the earliest date among all of that player's rows.
This is a textbook job for grouping. GROUP BY player_id bundles all rows of the same player together, and MIN(event_date) picks the smallest (earliest) date within each bundle.
Conceptually it scans every row once, keeping a running "best so far" date for each player and replacing it whenever an earlier date appears.
Example: player 1 has rows on 2016-03-01 and 2016-05-02; the minimum is 2016-03-01, which becomes their first login. Player 3's earliest of 2016-03-02 and 2018-07-03 is 2016-03-02.