Last updated on March 10th, 2025 at 11:12 pm

Here, we see the Game Play Analysis III LeetCode Solution. This Leetcode problem is solved using MySQL and Pandas.

List of all LeetCode Solution

Level of Question

Medium

Game Play Analysis III LeetCode Solution

1. Problem Statement

Column Name	Type
player_id	int
device_id	int
event_date	date
games_played	int

(player_id, event_date) is the primary key of this table. This table shows the activity of players of some game.

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 an SQL query that reports for each player and date, how many games played so far by the player. That is, the total number of games played by the player until that date. Check the example for clarity.

The query result format is in the following example:

Example 1:
Input:

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

Explanation:
For the player with id 1, 5 + 6 = 11 games played by 2016-05-02, and 5 + 6 + 1 = 12 games played by 2017-06-25.
For the player with id 3, 0 + 5 = 5 games played by 2018-07-03.
Note that for each player we only care about the days when the player logged in.

2. Code Implementation in Different Languages

2.1 Game Play Analysis III MySQL

select 
  player_id, 
  event_date, 
  games_played_so_far 
from 
  (
    select 
      player_id, 
      event_date, 
      @games := if(
        player_id = @player, @games + games_played, 
        games_played
      ) as games_played_so_far, 
      @player := player_id 
    from 
      (
        select 
          * 
        from 
          Activity 
        order by 
          player_id, 
          event_date
      ) as a, 
      (
        select 
          @player := -1, 
          @games := 0
      ) as tmp
  ) as t;