Loading problem...
You are building a gameplay analytics report for a game studio.
The table Activity stores one record for each player login day:
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:
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:
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}
]Explanation
Cumulative totals are computed independently for each player after ordering that player's rows by event_date.
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}
]Explanation
Input rows can arrive out of order. You must compute running totals in date order, not input order.
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}
]Explanation
Players are partitioned independently. Zero-day rows are still part of the cumulative timeline.
Constraints