Domain 1EasyCORE

Game Play Analysis II

The problem "Game Play Analysis II" involves analyzing game play data for users. Given two tables: `Activity` and `Players`, we need to find the first login date for each player.

Domain: Easy•Updated: 2026-08-31
#Algorithms#LeetCode#qwen2.5-coder:14b#**FAANG asked:** 0

512. Game Play Analysis II

Model: qwen2.5-coder:14b
Topics:
FAANG asked: 0
Acceptance rate: 56.1%


Problem Breakdown

The problem “Game Play Analysis II” involves analyzing game play data for users. Given two tables: Activity and Players, we need to find the first login date for each player.

Inputs:

  • Activity: A table with columns player_id (integer), device_id (integer), event_date (date), and game_played.
  • Players: A table with columns player_id (integer) and group_id (integer).

Outputs:

  • A table with two columns: player_id and first_login, where first_login is the earliest event_date for each player.

Implicit Constraints/Edge Cases:

  • The Activity table might be empty.
  • There could be duplicate entries for the same player on different dates.
  • The Players table might have players not present in the Activity table, and vice versa.

Intuition

The brute-force approach would involve iterating over each row in the Activity table to find the minimum event_date for each player_id. This would be inefficient with a time complexity of O(n^2) due to repeated scans.

A better approach is to use SQL aggregation functions, specifically MIN(), to efficiently find the first login date for each player. This leverages the database’s ability to perform operations in a single pass over the data.

Approach

  1. Join Tables: Perform an inner join between the Activity and Players tables on player_id to ensure we only consider players who have activity records.
  2. Group By Player: Group the results by player_id.
  3. Aggregate First Login Date: Use the MIN() function to find the earliest event_date for each player.
  4. Select Required Columns: Select player_id and the aggregated first login date.

Complexity Analysis

  • Time Complexity: O(n), where n is the number of rows in the Activity table. The join operation and aggregation are linear with respect to the input size.
  • Space Complexity: O(1) for the query itself, as it does not require additional space proportional to the input size.

Solution

import pandas as pd

def game_analysis(activity: pd.DataFrame, players: pd.DataFrame) -> pd.DataFrame:
    # Join the Activity and Players tables on player_id
    merged_df = activity.merge(players, on='player_id', how='inner')
    
    # Group by player_id and find the minimum event_date for each group
    result_df = merged_df.groupby('player_id')['event_date'].min().reset_index()
    
    # Rename the columns to match the required output format
    result_df.columns = ['player_id', 'first_login']
    
    return result_df

Edge Cases Handled

  • Empty Activity Table: The query will return an empty DataFrame since there are no records to process.
  • Duplicate Entries: The use of MIN() ensures that only the earliest login date is selected, effectively handling duplicates.
  • Players Without Activity: An inner join is used, so players without any activity records in the Activity table will not appear in the result.