Game Play Analysis I - Problem
You are given a table Activity that shows the activity of players of some games. Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on some day using some device.
Write a solution to find the first login date for each player.
Return the result table in any order.
Table Schema
Activity
| Column Name | Type | Description |
|---|---|---|
player_id
PK
|
int | Unique identifier for each player |
device_id
|
int | Device used by the player |
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 Logins Per Player
Input Table:
| player_id | device_id | event_date | games_played |
|---|---|---|---|
| 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:
| player_id | first_login |
|---|---|
| 1 | 2016-03-01 |
| 2 | 2017-06-25 |
| 3 | 2016-03-02 |
💡 Note:
Player 1 has two login records (2016-03-01 and 2016-05-02), so we take the earlier date. Player 2 has only one record. Player 3 has two records (2016-03-02 and 2018-07-03), so we take the earlier date.
Example 2 — Single Login Per Player
Input Table:
| player_id | device_id | event_date | games_played |
|---|---|---|---|
| 1 | 1 | 2020-01-15 | 3 |
| 2 | 2 | 2020-02-20 | 1 |
Output:
| player_id | first_login |
|---|---|
| 1 | 2020-01-15 |
| 2 | 2020-02-20 |
💡 Note:
Each player has only one login record, so their first_login is simply their only event_date.
Example 3 — Same Player Different Devices
Input Table:
| player_id | device_id | event_date | games_played |
|---|---|---|---|
| 1 | 1 | 2019-12-01 | 2 |
| 1 | 2 | 2019-11-15 | 4 |
| 1 | 3 | 2019-12-15 | 1 |
Output:
| player_id | first_login |
|---|---|
| 1 | 2019-11-15 |
💡 Note:
Player 1 used different devices on different dates. We find their earliest login date regardless of device used.
Constraints
-
1 ≤ player_id ≤ 10^5 -
1 ≤ device_id ≤ 10^4 -
event_dateis a valid date -
0 ≤ games_played ≤ 1000
Visualization
Tap to expand
Understanding the Visualization
1
Input
Activity table with multiple login records per player
2
Group & Aggregate
GROUP BY player_id and MIN(event_date)
3
Output
First login date for each player
Key Takeaway
🎯 Key Insight: Use GROUP BY with MIN() aggregate function when you need to find the earliest/minimum value per group
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code