Game Play Analysis I - Practice Coding Problems

Game Play Analysis I - Problem

Database Easy

You are analyzing player activity data for a popular gaming platform. The platform tracks when players log in and play games throughout different days.

Your task: Find the first login date for each player in the database.

You are given an Activity table with the following structure:

Column Name	Type
player_id	int
device_id	int
event_date	date
games_played	int

Key Information:

The combination of (player_id, event_date) is unique (primary key)
Each row represents a player's activity on a specific date
A player might have logged in multiple times on different dates
games_played can be 0 (player logged in but didn't play any games)

Goal: Return a table with player_id and their first_login_date. The order of results doesn't matter.

Input & Output

basic_example.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 | | 2 | 3 | 2017-06-25 | 1 | | 3 | 1 | 2016-03-02 | 0 | | 3 | 4 | 2018-07-03 | 5 |

› Output: | player_id | first_login_date | |-----------|-----------------| | 1 | 2016-03-01 | | 2 | 2017-06-25 | | 3 | 2016-03-02 |

💡 Note: Player 1 first logged in on 2016-03-01, player 2 has only one record so that's their first login, and player 3 first logged in on 2016-03-02 (earlier than their 2018 login).

single_login.sql — Single Login Per Player

$ Input: Activity table: | player_id | device_id | event_date | games_played | |-----------|-----------|------------|--------------| | 1 | 1 | 2020-01-15 | 3 | | 2 | 2 | 2020-02-20 | 0 | | 3 | 3 | 2020-03-25 | 7 |

› Output: | player_id | first_login_date | |-----------|-----------------| | 1 | 2020-01-15 | | 2 | 2020-02-20 | | 3 | 2020-03-25 |

💡 Note: Each player has only one login record, so their first login date is simply their only login date. Note that games_played can be 0.

multiple_logins.sql — Multiple Logins Edge Case

$ Input: Activity table: | player_id | device_id | event_date | games_played | |-----------|-----------|------------|--------------| | 1 | 1 | 2019-12-31 | 1 | | 1 | 2 | 2020-01-01 | 2 | | 1 | 1 | 2019-11-15 | 0 | | 2 | 3 | 2020-06-01 | 5 | | 2 | 3 | 2020-05-31 | 3 |

› Output: | player_id | first_login_date | |-----------|-----------------| | 1 | 2019-11-15 | | 2 | 2020-05-31 |

💡 Note: Player 1 has three logins, with the earliest being 2019-11-15. Player 2 has two logins with 2020-05-31 being earlier than 2020-06-01. The MIN function correctly identifies the earliest date for each player.

Visualization

Tap to expand

Understanding the Visualization

Organize by Member

Group all logbook entries by member ID, creating separate piles for each person

Find Earliest Date

In each member's pile, look through their visit dates and circle the earliest one

Create Anniversary List

Write down each member's ID and their first visit date for the celebration

Key Takeaway

🎯 Key Insight: SQL's GROUP BY with MIN() function is like having a super-efficient club secretary who automatically organizes member records and finds the earliest date for each person instantly.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Database engine typically sorts data for GROUP BY operations, though some engines use hash-based grouping

⚡ Linearithmic

Space Complexity

O(k)

Where k is the number of unique players (for storing intermediate groups)

✓ Linear Space

Constraints

1 ≤ Activity table rows ≤ 10⁴
1 ≤ player_id ≤ 10⁴
1 ≤ device_id ≤ 10⁴
0 ≤ games_played ≤ 1000
event_date is in format YYYY-MM-DD
The combination (player_id, event_date) is unique

Asked in

f Facebook 45 a Amazon 32 ⊞ Microsoft 28 G Google 25

The optimal solution uses GROUP BY with MIN aggregate function. Group all activity records by player_id and apply MIN(event_date) to find each player's earliest login date. This leverages SQL's built-in optimization for aggregate operations, making it much more efficient than self-joins or nested queries.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Self-Join Approach)	O(n²)	O(n)	Compare each player's activity with all other activities to find the minimum date
GROUP BY with MIN (Optimal)	O(n log n)	O(k)	Use SQL GROUP BY with MIN aggregate function to find earliest date per player

Brute Force (Self-Join Approach) — Algorithm Steps

Join the Activity table with itself on player_id
For each activity record, check if there's any earlier date for the same player
Keep only records where no earlier date exists
Select distinct player_id and event_date pairs

Visualization

Tap to expand

Step-by-Step Walkthrough

Setup Self-Join

Create two instances of Activity table (a1 and a2)

Compare Dates

For each record in a1, check all records in a2 for the same player

Filter Results

Keep only records where no earlier date exists

Code -

solution.c — C

// SQL Solution (C with database library)
#include <stdio.h>
#include <string.h>

char* solution() {
    static char query[] = "SELECT DISTINCT a1.player_id, a1.event_date as first_login_date "
                         "FROM Activity a1 "
                         "WHERE NOT EXISTS ( "
                         "    SELECT 1 FROM Activity a2 "
                         "    WHERE a2.player_id = a1.player_id "
                         "    AND a2.event_date < a1.event_date "
                         ") "
                         "ORDER BY a1.player_id;";
    return query;
}

int main() {
    printf("%s\n", solution());
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Self-join creates n² comparisons where n is the number of activity records

⚠ Quadratic Growth

Space Complexity

O(n)

Temporary result set for join operations

⚡ Linearithmic Space

Constraints

1 ≤ Activity table rows ≤ 10⁴
1 ≤ player_id ≤ 10⁴
1 ≤ device_id ≤ 10⁴
0 ≤ games_played ≤ 1000
event_date is in format YYYY-MM-DD
The combination (player_id, event_date) is unique

68.4K Views

High Frequency

~8 min Avg. Time

2.8K 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Self-Join Approach) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler