Game Play Analysis II - Practice Coding Problems

Game Play Analysis II - Problem

Database Easy

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

Understanding the Visualization

Input

Activity table with player sessions

Window Function

Rank sessions by date per player

Output

Player and their first device

Key Takeaway

🎯 Key Insight: Use window functions to find the first occurrence within each group efficiently

Asked in

A Amazon 25 F Facebook 18

Use ROW_NUMBER() window function with PARTITION BY player_id ORDER BY event_date to rank sessions by date for each player, then filter for rn = 1 to get the first login device.

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

Common Approaches

Approach	Time	Space	Notes
✓ Window Function with ROW_NUMBER	O(n log n)	O(n)	Use ROW_NUMBER() to rank sessions by date for each player and select the first one

Window Function with ROW_NUMBER — Algorithm Steps

Step 1: Partition data by player_id and order by event_date using ROW_NUMBER()
Step 2: Filter results where row_number = 1 to get first login session
Step 3: Select player_id and device_id from the filtered results

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition

Group by player_id

Order

Sort by event_date within each partition

Rank

Assign row numbers, filter rn=1

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting required for ordering by event_date within partitions

⚡ Linearithmic

Space Complexity

O(n)

Storage for row number calculations

⚡ Linearithmic Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Window Function with ROW_NUMBER — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler