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 Games INPUT Activity Table player_id event_date games_played 1 2016-03-01 5 1 2016-03-02 6 1 2016-03-03 1 2 2016-03-01 3 2 2016-03-02 4 Player Timeline P1: 5 6 1 P2: 3 4 Games per day for each player Need: Running totals ALGORITHM STEPS 1 Self Join Table Join Activity a1 with a2 on same player_id 2 Filter Dates a2.date <= a1.date Get all prior dates 3 Group By GROUP BY player, date from a1 table 4 Sum Games SUM(a2.games_played) = running total SELECT a1.player_id, a1.event_date, SUM(a2.games_played) FROM Activity a1, a2 WHERE a1.id=a2.id AND a2.date<=a1.date FINAL RESULT Cumulative Games Table player_id event_date games_total 1 2016-03-01 5 1 2016-03-02 11 1 2016-03-03 12 2 2016-03-01 3 2 2016-03-02 7 Calculation Breakdown Player 1: Day1: 5 = 5 Day2: 5+6 = 11 Day3: 5+6+1 = 12 Player 2: Day1: 3 Day2: 3+4 = 7 OK - Complete Key Insight: Self-join with date filtering creates all (current_date, prior_date) pairs for each player. Summing games_played from matched rows gives running total. Alternative: Use window function SUM(games_played) OVER (PARTITION BY player_id ORDER BY event_date) for better performance. TutorialsPoint - Game Play Analysis III | Optimal Solution (Self-Join / Window Function)

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