Game Play Analysis III
Problem
Return (player_id, event_date, games_played_so_far) where games_played_so_far is the cumulative games_played up to and including event_date.
rows = [(1,'2016-03-01',5),(1,'2016-05-02',6),(1,'2017-06-25',1),(3,'2016-03-02',0),(3,'2018-07-03',5)][(1,'2016-03-01',5),(1,'2016-05-02',11),(1,'2017-06-25',12),(3,'2016-03-02',0),(3,'2018-07-03',5)]SELECT player_id, event_date,
SUM(games_played) OVER (PARTITION BY player_id ORDER BY event_date) AS games_played_so_far
FROM Activity;Explanation
For every row we want a running total of games played by that player up to and including that date. This is a perfect fit for a window function, which computes a value over a sliding set of rows without collapsing them.
The expression SUM(games_played) OVER (PARTITION BY player_id ORDER BY event_date) reads as: split the rows into groups by player (PARTITION BY), sort each group by date (ORDER BY), and for each row add up games_played from the first date through the current one.
Unlike a plain GROUP BY, the window keeps every row in the output and just attaches the cumulative number to it.
Example: player 1 plays 5, then 6, then 1 over three dates, so the running totals come out 5, 11, 12. Player 3 is a separate partition and starts its own count at 0.