Game Play Analysis III - Problem

You are given a table Activity that tracks game playing activity for multiple players across different dates.

Table Structure:

  • player_id (int): Unique identifier for each player
  • device_id (int): Device used by the player
  • event_date (date): Date when the player logged in
  • games_played (int): Number of games played on that date

Task: Write a SQL query to calculate the cumulative total of games played by each player up to each date. For every player and date combination, show how many games that player has played in total from their first login until that specific date.

The result should include player_id, event_date, and games_played_so_far (the running total).

Table Schema

Activity
Column Name Type Description
player_id PK int Unique identifier for each player
device_id int Device ID used by the player
event_date PK date Date when the player logged in and played games
games_played int Number of games played on that specific date
Primary Key: (player_id, event_date)
Note: Each row represents a gaming session for a player on a specific date

Input & Output

Example 1 — Multiple Players with Running Totals
Input Table:
player_id device_id event_date games_played
1 2 2016-03-01 5
1 2 2016-03-02 6
2 3 2017-06-25 1
3 1 2016-03-02 0
3 4 2018-07-03 5
Output:
player_id event_date games_played_so_far
1 2016-03-01 5
1 2016-03-02 11
2 2017-06-25 1
3 2016-03-02 0
3 2018-07-03 5
💡 Note:

For player 1: First day played 5 games (total=5), second day played 6 more games (total=5+6=11). For player 2: Only played once with 1 game (total=1). For player 3: First day played 0 games (total=0), later played 5 games (total=0+5=5).
Example 2 — Single Player Multiple Sessions
Input Table:
player_id device_id event_date games_played
1 1 2020-01-01 3
1 1 2020-01-02 2
1 2 2020-01-03 4
Output:
player_id event_date games_played_so_far
1 2020-01-01 3
1 2020-01-02 5
1 2020-01-03 9
💡 Note:

Player 1's cumulative game count: Day 1 = 3, Day 2 = 3+2 = 5, Day 3 = 5+4 = 9. The running total increases with each gaming session.

Constraints

  • 1 ≤ player_id ≤ 10^4
  • 1 ≤ device_id ≤ 10^4
  • games_played ≥ 0
  • event_date is a valid date

Visualization

Tap to expand
Game Play Analysis III: Cumulative GamesInput: Gaming Sessionsplayer_iddategamesdevice103-0152103-0262206-2513SUM() OVERPARTITION BY player_idOutput: Cumulative Totalsplayer_iddatetotal_games103-015103-0211206-251Player 1: 5 games → 11 totalRunning sum per playerWindow Function LogicPARTITION BY player_idORDER BY event_dateSUM(games_played)
Understanding the Visualization
1
Input
Player gaming sessions with dates and game counts
2
Window Function
Calculate running sum partitioned by player
3
Output
Cumulative games played by each player over time
Key Takeaway
🎯 Key Insight: Window functions with PARTITION BY create running totals within groups efficiently

Related Problems

Asked in
Amazon 25 Facebook 18 Microsoft 15
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen