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. Cumulative Sum-Running of games played
Example :
Table: Activity
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2020-12-01 | 5 |
| 1 | 2 | 2021-01-02 | 6 |
| 2 | 3 | 2021-01-04 | 1 |
| 3 | 1 | 2020-12-02 | 8 |
| 3 | 4 | 2020-12-25 | 5 |
+-----------+-----------+------------+--------------+
Result
+-----------+-------------+---------------+
| player_id | event_login | games_played | |
+-----------+-------------+---------------+
| 1 | 2020-12-01 | 5 |
| 1 | 2021-01-02 | 11 |
| 2 | 2021-01-04 | 1 |
| 3 | 2020-12-02 | 8 |
| 3 | 2020-12-25 | 13 |
+-----------+-------------+---------------+
Approach:
Mysql
select t1.player_id, t1.event_date, sum(t2.games_played)
as games_played
from Activity as t1 inner join Activity as t2
on t1.player_id = t2.player_id
where t1.event_date >= t2.event_date
group by t1.event_date,t1.player_id
Oracle
select t1.player_id, t1.event_date, sum(t2.games_played)
as games_played
from Activity as t1 inner join Activity as t2
on t1.player_id = t2.player_id
where t1.event_date >= t2.event_date
group by t1.event_date,t1.player_id
No comments:
Post a Comment