Game Play Analysis II - Problem

Given a table Activity that tracks player game sessions, write a SQL query to find the first device each player used when they logged in.

The Activity table contains:

  • player_id - unique identifier for each player
  • device_id - device used for the session
  • event_date - date of the gaming session
  • games_played - number of games played in that session

Return the result showing each player and their first login device in any order.

Table Schema

Activity
Column Name Type Description
player_id PK int Unique identifier for each player
device_id int Device identifier used for the session
event_date PK date Date when the player logged in
games_played int Number of games played in the session
Primary Key: (player_id, event_date)
Note: Each row represents a gaming session for a player on a specific date

Input & Output

Example 1 — Multiple Players with Multiple Sessions
Input Table:
player_id device_id event_date games_played
1 2 2016-03-01 5
1 3 2016-03-02 6
2 4 2017-06-25 1
Output:
player_id device_id
1 2
2 4
💡 Note:

Player 1 first logged in on 2016-03-01 using device 2, then again on 2016-03-02 using device 3. Player 2 only has one session on 2017-06-25 using device 4. The output shows each player's first login device.
Example 2 — Single Player Multiple Devices
Input Table:
player_id device_id event_date games_played
3 1 2018-07-03 0
3 4 2018-07-01 5
Output:
player_id device_id
3 4
💡 Note:

Player 3 has two sessions: one on 2018-07-01 with device 4 and another on 2018-07-03 with device 1. Since the session on 2018-07-01 is earlier, device 4 is the first device used by player 3.

Constraints

  • 1 ≤ player_id ≤ 1000
  • 1 ≤ device_id ≤ 1000
  • event_date is a valid date
  • 0 ≤ games_played ≤ 100

Visualization

Tap to expand
Game Play Analysis II INPUT: Activity Table player_id device_id event_date games 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 Primary Key: (player_id, event_date) Goal: Find first device for each player P1 P2 P3 3 unique players ALGORITHM STEPS 1 Subquery: Min Date Find earliest event_date per player using MIN() 2 GROUP BY player_id Aggregate to get one row per player 3 JOIN with Activity Match on player_id AND event_date 4 SELECT device_id Get device from the first login record SELECT a.player_id, a.device_id FROM Activity a JOIN (subquery) b ON... FINAL RESULT player_id device_id 1 2 2 3 3 1 Each player's FIRST login device identified Player 1 2016-03-01 Device 2 OK Later logins excluded Only first login kept Key Insight: Use a correlated subquery or self-join to find the minimum event_date per player first, then join back to get the device_id from that specific record. This ensures we get the device from the FIRST login, not just any device the player has used. TutorialsPoint - Game Play Analysis II | Optimal Solution

Related Problems

Asked in
Amazon 25 Facebook 18
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen