Game Play Analysis III - Solution & Explanation

Problem Overview: The Activity table records how many games each player plays on a given date. For every row, return the cumulative number of games played by that player up to that date. The result must include player_id, event_date, and the running total of games.

Approach 1: Correlated Subquery (Default Approach) (Time: O(n^2), Space: O(1))

This approach calculates the cumulative total using a correlated subquery. For each row in the table, run a subquery that sums games_played for the same player_id where event_date is less than or equal to the current row’s date. The database repeatedly scans matching rows to compute the running total. The logic is simple and mirrors how you would compute prefix sums manually, but it performs poorly on large datasets because each row triggers another aggregation.

Approach 2: Self Join + Group By (Time: O(n^2), Space: O(n))

Join the Activity table with itself on matching player_id values where the joined row’s event_date is less than or equal to the base row’s date. After the join, aggregate with SUM(games_played) and group by player_id and event_date. Each row effectively gathers all earlier rows for the same player before computing the total. This technique avoids correlated subqueries and works in older SQL engines that lack window functions, but the join size can grow quickly as the dataset increases.

Approach 3: Window Function (Optimal) (Time: O(n log n), Space: O(n))

The optimal solution uses a window function with SUM(games_played) OVER (PARTITION BY player_id ORDER BY event_date). The PARTITION BY clause groups rows by player, and the ORDER BY clause processes rows chronologically within each player’s partition. The database engine computes a running sum as it scans the ordered rows, eliminating repeated joins or subqueries. This approach is concise, efficient, and the standard way to compute cumulative metrics in modern SQL systems. Window functions are commonly used for analytics queries involving running totals, rankings, or moving averages.

Understanding window functions is critical for solving advanced problems in SQL and database interviews. The same pattern appears in financial reporting, event tracking, and analytics dashboards. If you frequently work with cumulative calculations, mastering window functions significantly simplifies query design.

Recommended for interviews: The window function approach is the expected solution. It demonstrates strong SQL fundamentals and familiarity with analytical queries. Mentioning the self-join or correlated subquery approach first can show you understand how cumulative totals work conceptually, but the window function shows practical SQL expertise.