Player Cumulative Gameplay

Medium

You are building a gameplay analytics report for a game studio. The table Activity stores one record for each player login day: player_id: integer identifier of the player device_id: identifier of the device used that day event_date: calendar date of the session games_played: number of matches completed on that date The pair (player_id, event_date) is unique. For each row in Activity, compute how many total games that player has completed up to and including that event_date. Return a result table with exactly these columns: player_id event_date games_played_so_far The final row order is not semantically important, but each row must have the correct cumulative total for its player. This problem should be solvable in both: SQL (SQLite 3.27.2) Pandas

Example 1

Input

Activity:
| player_id | device_id | event_date  | games_played |
|-----------|-----------|-------------|--------------|
| 1         | 2         | 2016-03-01  | 5            |
| 1         | 2         | 2016-05-02  | 6            |
| 1         | 3         | 2017-06-25  | 1            |
| 3         | 1         | 2016-03-02  | 0            |
| 3         | 4         | 2018-07-03  | 5            |

Output

[
  {"player_id":1,"event_date":"2016-03-01","games_played_so_far":5},
  {"player_id":1,"event_date":"2016-05-02","games_played_so_far":11},
  {"player_id":1,"event_date":"2017-06-25","games_played_so_far":12},
  {"player_id":3,"event_date":"2016-03-02","games_played_so_far":0},
  {"player_id":3,"event_date":"2018-07-03","games_played_so_far":5}
]

Explanation

Cumulative totals are computed independently for each player after ordering that player's rows by event_date.

Example 2

Input

Activity:
| player_id | device_id | event_date  | games_played |
|-----------|-----------|-------------|--------------|
| 8         | 9         | 2021-01-03  | 2            |
| 8         | 9         | 2021-01-01  | 4            |
| 8         | 7         | 2021-01-02  | 1            |

Output

[
  {"player_id":8,"event_date":"2021-01-01","games_played_so_far":4},
  {"player_id":8,"event_date":"2021-01-02","games_played_so_far":5},
  {"player_id":8,"event_date":"2021-01-03","games_played_so_far":7}
]

Explanation

Input rows can arrive out of order. You must compute running totals in date order, not input order.

Example 3

Input

Activity:
| player_id | device_id | event_date  | games_played |
|-----------|-----------|-------------|--------------|
| 20        | 3         | 2022-10-10  | 0            |
| 20        | 4         | 2022-10-11  | 0            |
| 21        | 2         | 2022-10-10  | 7            |

Output

[
  {"player_id":20,"event_date":"2022-10-10","games_played_so_far":0},
  {"player_id":20,"event_date":"2022-10-11","games_played_so_far":0},
  {"player_id":21,"event_date":"2022-10-10","games_played_so_far":7}
]

Explanation

Players are partitioned independently. Zero-day rows are still part of the cumulative timeline.

Constraints

1 <= number of rows in Activity <= 50,000
1 <= player_id <= 1,000,000,000
1 <= device_id <= 1,000,000,000
event_date uses ISO date format YYYY-MM-DD
0 <= games_played <= 100,000
(player_id, event_date) is unique
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: player_id, event_date, games_played_so_far

Activity: | player_id | device_id | event_date | games_played | |-----------|-----------|-------------|--------------| | 1 | 2 | 2016-03-01 | 5 | | 1 | 2 | 2016-05-02 | 6 | | 1 | 3 | 2017-06-25 | 1 | | 3 | 1 | 2016-03-02 | 0 | | 3 | 4 | 2018-07-03 | 5 |

[ {"player_id":1,"event_date":"2016-03-01","games_played_so_far":5}, {"player_id":1,"event_date":"2016-05-02","games_played_so_far":11}, {"player_id":1,"event_date":"2017-06-25","games_played_so_far":12}, {"player_id":3,"event_date":"2016-03-02","games_played_so_far":0}, {"player_id":3,"event_date":"2018-07-03","games_played_so_far":5} ]

Activity: | player_id | device_id | event_date | games_played | |-----------|-----------|-------------|--------------| | 8 | 9 | 2021-01-03 | 2 | | 8 | 9 | 2021-01-01 | 4 | | 8 | 7 | 2021-01-02 | 1 |

[ {"player_id":8,"event_date":"2021-01-01","games_played_so_far":4}, {"player_id":8,"event_date":"2021-01-02","games_played_so_far":5}, {"player_id":8,"event_date":"2021-01-03","games_played_so_far":7} ]

Activity: | player_id | device_id | event_date | games_played | |-----------|-----------|-------------|--------------| | 20 | 3 | 2022-10-10 | 0 | | 20 | 4 | 2022-10-11 | 0 | | 21 | 2 | 2022-10-10 | 7 |

[ {"player_id":20,"event_date":"2022-10-10","games_played_so_far":0}, {"player_id":20,"event_date":"2022-10-11","games_played_so_far":0}, {"player_id":21,"event_date":"2022-10-10","games_played_so_far":7} ]