Game Play Analysis III

Game Play Analysis III

What is this problem about?

The Game Play Analysis III interview question asks you to calculate the cumulative sum (running total) of games played by each player for every date they logged in. The output should include the player_id, the event_date, and the total number of games played by that player up to and including that date.

Why is this asked in interviews?

This is a classic Database coding problem to test a candidate's knowledge of Window Functions, specifically for calculating running totals. While it can be solved with self-joins, that approach is outdated and inefficient. Interviewers want to see if you are familiar with modern SQL analytics functions that handle cumulative data seamlessly.

Algorithmic pattern used

The standard pattern here is the Cumulative Sum Window Function.

1. Use the SUM() aggregate function, but apply it as a window function using the OVER() clause.
2. Partition the data by player_id so the sum resets for each new player.
3. Order the window by event_date to ensure the sum accumulates chronologically. SUM(games_played) OVER (PARTITION BY player_id ORDER BY event_date) AS games_played_so_far

Example explanation

Player 1 Logs:

• 2016-03-01: 5 games
• 2016-05-02: 6 games
• 2017-06-25: 1 game

Window Function Execution:

1. Row 1 (March 1): Sum is just the current row. Total = 5.
2. Row 2 (May 2): Sum is previous rows + current row. Total = 5 + 6 = 11.
3. Row 3 (June 25): Sum is all previous + current. Total = 11 + 1 = 12.

Common mistakes candidates make

• Self-Join Approach: Doing a JOIN where a.player_id = b.player_id AND a.event_date >= b.event_date and then grouping. This works but has $O(N^2)$ complexity per player, which is terrible for large datasets.
• Forgetting ORDER BY in OVER: If you write OVER(PARTITION BY player_id), it will just return the grand total for the player on every row, not a running total. The ORDER BY is required to frame the window chronologically.

Interview preparation tip

Memorize the syntax for running totals: SUM(col) OVER (PARTITION BY group_col ORDER BY time_col). It is one of the top 3 most useful window functions to know for data interviews.