Game Play Analysis III - Practice Coding Problems

Game Play Analysis III - Problem

Database Medium

Game Play Analysis III - Calculate Running Totals of Games Played

You're analyzing player activity data for a mobile gaming platform. Given a table of player login records, you need to calculate running totals of games played by each player over time.

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+

The primary key is (player_id, event_date). Each row represents a player's gaming session on a specific date.

Goal: For each player and date, calculate the cumulative total of games played from their first login up to that date. This gives us insights into player engagement patterns over time.

Example: If Player 1 played 5 games on Jan 1st and 3 games on Jan 3rd, the result should show 5 games for Jan 1st and 8 games (5+3) for Jan 3rd.

Input & Output

example_1.sql — Basic Player Activity

$ 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 | event_date | games_played_so_far | +-----------+------------+---------------------+ | 1 | 2016-03-01 | 5 | | 1 | 2016-05-02 | 11 | | 2 | 2017-06-25 | 1 | | 3 | 2016-03-02 | 0 | | 3 | 2018-07-03 | 5 | +-----------+------------+---------------------+

💡 Note: Player 1: First login with 5 games (total: 5), second login with 6 more games (total: 5+6=11). Player 2: Only one session with 1 game. Player 3: First session with 0 games, second session adds 5 games for a total of 5.

example_2.sql — Single Player Multiple Days

$ Input: Activity table: +-----------+-----------+------------+--------------+ | player_id | device_id | event_date | games_played | +-----------+-----------+------------+--------------+ | 1 | 1 | 2020-01-01 | 3 | | 1 | 1 | 2020-01-02 | 2 | | 1 | 1 | 2020-01-03 | 1 | +-----------+-----------+------------+--------------+

› Output: +-----------+------------+---------------------+ | player_id | event_date | games_played_so_far | +-----------+------------+---------------------+ | 1 | 2020-01-01 | 3 | | 1 | 2020-01-02 | 5 | | 1 | 2020-01-03 | 6 | +-----------+------------+---------------------+

💡 Note: Player 1 plays consistently over 3 days. Running totals: Day 1: 3 games, Day 2: 3+2=5 games, Day 3: 3+2+1=6 games total.

example_3.sql — Zero Games Edge Case

$ Input: Activity table: +-----------+-----------+------------+--------------+ | player_id | device_id | event_date | games_played | +-----------+-----------+------------+--------------+ | 1 | 1 | 2021-01-01 | 0 | | 1 | 1 | 2021-01-02 | 0 | | 1 | 1 | 2021-01-03 | 5 | +-----------+-----------+------------+--------------+

› Output: +-----------+------------+---------------------+ | player_id | event_date | games_played_so_far | +-----------+------------+---------------------+ | 1 | 2021-01-01 | 0 | | 1 | 2021-01-02 | 0 | | 1 | 2021-01-03 | 5 | +-----------+------------+---------------------+

💡 Note: Player 1 logs in but doesn't play games for first two days (running total stays 0), then plays 5 games on third day (total becomes 5).

Visualization

Tap to expand

Understanding the Visualization

Group by Account (Player)

Separate transactions by account holder, like grouping bank statements by customer

Sort by Date

Arrange transactions chronologically to see the sequence of events

Calculate Running Balance

Add each transaction to the previous balance to get the new total

Display Statement

Show date, transaction amount, and running balance for each entry

Key Takeaway

🎯 Key Insight: Window functions eliminate the need for self-joins by efficiently calculating running sums in a single pass, making this optimal for large datasets just like modern banking systems process millions of transactions efficiently.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single scan with sorting by player and date. Database optimizes the window function execution

⚡ Linearithmic

Space Complexity

O(n)

Space for sorting and intermediate window function calculations

⚡ Linearithmic Space

Constraints

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

Asked in

a Amazon 25 G Google 18 f Meta 12 ⊞ Microsoft 8

The optimal solution uses SQL window functions or grouped cumulative sums to calculate running totals efficiently. Key insight: SUM() OVER(PARTITION BY player_id ORDER BY event_date) computes cumulative games for each player in O(n log n) time with a single table scan.

Common Approaches

Approach	Time	Space	Notes
✓ Window Function (Optimal)	O(n log n)	O(n)	Use SQL window function to calculate running sum in single pass
Self-Join Approach	O(n²)	O(n)	Join table with itself to sum all previous games for each record

Window Function (Optimal) — Algorithm Steps

Partition data by player_id to group each player's records
Order by event_date within each partition
Apply SUM() window function with ROWS UNBOUNDED PRECEDING
Get running total for each row efficiently

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition by Player

Group records by player_id

Order by Date

Sort each player's records chronologically

Apply Window Function

Calculate running sum for each partition

Generate Results

Output cumulative totals efficiently

Code -

solution.c — C

// C solution using arrays for running totals
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define MAX_PLAYERS 1000

typedef struct {
    int player_id;
    int device_id;
    char event_date[12];
    int games_played;
} Activity;

typedef struct {
    int player_id;
    char event_date[12];
    int games_played_so_far;
} Result;

int compareActivities(const void* a, const void* b) {
    Activity* actA = (Activity*)a;
    Activity* actB = (Activity*)b;
    
    if (actA->player_id != actB->player_id) {
        return actA->player_id - actB->player_id;
    }
    return strcmp(actA->event_date, actB->event_date);
}

Result* gameAnalysisOptimal(Activity* activityData, int size, int* returnSize) {
    // Sort activities
    qsort(activityData, size, sizeof(Activity), compareActivities);
    
    Result* result = (Result*)malloc(size * sizeof(Result));
    int playerTotals[MAX_PLAYERS] = {0};
    *returnSize = size;
    
    for (int i = 0; i < size; i++) {
        playerTotals[activityData[i].player_id] += activityData[i].games_played;
        
        result[i].player_id = activityData[i].player_id;
        strcpy(result[i].event_date, activityData[i].event_date);
        result[i].games_played_so_far = playerTotals[activityData[i].player_id];
    }
    
    return result;
}

int main() {
    // Input parsing implementation
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single scan with sorting by player and date. Database optimizes the window function execution

⚡ Linearithmic

Space Complexity

O(n)

Space for sorting and intermediate window function calculations

⚡ Linearithmic Space

Constraints

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

28.4K Views

Medium Frequency

~15 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Window Function (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler