00:00:00

Description

Editorial

Player Cumulative Gameplay

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

activity =

[{"player_id":1,"device_id":2,"event_date":"2016-03-01","games_played":5},{"player_id":1,"device_id":2,"event_date":"2016-05-02","games_played":6},{"player_id":1,"device_id":3,"event_date":"2017-06-25","games_played":1},{"player_id":3,"device_id":1,"event_date":"2016-03-02","games_played":0},{"player_id":3,"device_id":4,"event_date":"2018-07-03","games_played":5}]

Loading problem...

Player Cumulative Gameplay (Medium) — Practice with Code Visualizer | OneNoughtOne

101

00:00:00

Description

Editorial

Player Cumulative Gameplay

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

activity =

Player Cumulative Gameplay

Hints

Player Cumulative Gameplay

Hints