Game Play Analysis II - Problem
🎮 Game Play Analysis II
You're working as a data analyst for a popular gaming platform! Players log in from different devices to play games, and you need to track their gaming habits.
Given the Activity table that records player login sessions:
| Column Name | Type |
|---|---|
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
Your mission: Find the first device that each player used to log in to the platform.
Key Details:
- Each row represents a gaming session where a player logged in using a specific device
- Players can use multiple devices over time
- The combination of (player_id, event_date) is unique
- You need to return the device_id of the earliest login for each player
Think of it like finding each player's "home device" - the very first one they used!
Input & Output
example_1.sql — Basic Case
$
Input:
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 1 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
›
Output:
+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
| 1 | 2 |
| 3 | 1 |
+-----------+-----------+
💡 Note:
Player 1 first logged in on 2016-03-01 using device 2, and Player 3 first logged in on 2016-03-02 using device 1. These are their respective first devices.
example_2.sql — Single Player Multiple Devices
$
Input:
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 5 | 10 | 2020-01-15 | 3 |
| 5 | 11 | 2020-01-10 | 2 |
| 5 | 12 | 2020-01-20 | 1 |
+-----------+-----------+------------+--------------+
›
Output:
+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
| 5 | 11 |
+-----------+-----------+
💡 Note:
Player 5 used three different devices, but the earliest login was on 2020-01-10 with device 11, making it their first device.
example_3.sql — Same Date Different Players
$
Input:
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 2 | 5 | 2019-06-01 | 0 |
| 4 | 5 | 2019-06-01 | 2 |
| 2 | 7 | 2019-06-02 | 1 |
+-----------+-----------+------------+--------------+
›
Output:
+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
| 2 | 5 |
| 4 | 5 |
+-----------+-----------+
💡 Note:
Both players 2 and 4 first logged in on the same date (2019-06-01) but used the same device 5. Player 2's later session with device 7 is ignored since it's not their first.
Constraints
- 1 ≤ player_id, device_id ≤ 104
- event_date is in the format YYYY-MM-DD
- 0 ≤ games_played ≤ 1000
- (player_id, event_date) is the primary key
- At least 1 row in the Activity table
Visualization
Tap to expand
Understanding the Visualization
1
Group by Players
Separate all gaming sessions by player ID, like organizing different players' gaming histories
2
Sort Chronologically
Within each player's group, sort all their sessions by date from earliest to latest
3
Rank Sessions
Assign rank 1 to the earliest session, rank 2 to second earliest, etc.
4
Extract First Device
Keep only rank 1 sessions to get each player's first device
Key Takeaway
🎯 Key Insight: Window functions eliminate the need for expensive correlated subqueries by efficiently ranking records within partitions, making complex analytical queries both readable and performant.
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code