Problem

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
(player_id, event_date) is the primary key (column with unique values) of this table.
This table shows the activity of players of some games.
Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on someday using some device.

Problem Definition

Write a solution to report for each player and date, how many games_played_so_far by the player. That is, the total number of games played by the player until that date.

Example

Image scaled to 70%

Output

Image scaled to 60%

Try It Yourself

Database Exercise

Database Schema:

-- Database schema would be rendered here

Exercise Script:

-- Exercise script would be rendered here

Available actions: Execute

Solution

To report the cumulative number of games each player has played up to each event date, we can utilize SQL’s window functions. Specifically, the SUM() window function with appropriate partitioning and ordering allows us to calculate a running total (games_played_so_far) for each player across different dates.

• Select Relevant Columns: Retrieve player_id and event_date to identify each player’s activity on specific dates.
• Calculate Cumulative Games Played: Use the SUM() window function to compute a running total of games_played for each player, ordered by event_date.
• Order the Results: Although not required, ordering the results by player_id and event_date can enhance readability.

SQL Query

SELECT player_id,
       event_date,
       SUM(games_played) OVER (
           PARTITION BY player_id
           ORDER BY event_date
       ) AS games_played_so_far
FROM   Activity;

Step-by-Step Approach

Step 1: Select Player ID and Event Date

Retrieve each player’s unique identifier (player_id) and the corresponding date (event_date) of their activity.

SQL Query:

SELECT player_id,
       event_date
FROM   Activity;

Explanation:

• SELECT player_id, event_date:

  • Chooses the player_id and event_date columns to identify each player’s activity on specific dates.

• FROM Activity:

  • Specifies the Activity table as the source of the data.

Output After Step 1:

+-----------+------------+
| player_id | event_date |
+-----------+------------+
| 1         | 2016-03-01 |
| 1         | 2016-05-02 |
| 1         | 2017-06-25 |
| 3         | 2016-03-02 |
| 3         | 2018-03-07 |
+-----------+------------+

Step 2: Calculate Cumulative Games Played

Compute the total number of games each player has played up to and including each event_date.

SQL Query:

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:

• SUM(games_played) OVER (PARTITION BY player_id ORDER BY event_date) AS games_played_so_far:

  • SUM(games_played) OVER (...):

    • Applies the SUM() window function to calculate a running total of games_played.

  • PARTITION BY player_id:

    • Divides the dataset into partitions for each player_id, ensuring that the cumulative sum is calculated separately for each player.

  • ORDER BY event_date:

    • Orders the events chronologically within each partition to ensure the running total accumulates correctly over time.

  • AS games_played_so_far:

    • Assigns an alias to the calculated cumulative sum for clarity in the results.

Output After Step 2:

+-----------+------------+--------------------+
| player_id | event_date | games_played_so_far|
+-----------+------------+--------------------+
| 1         | 2016-03-01 | 5                  |
| 1         | 2016-05-02 | 11                 |
| 1         | 2017-06-25 | 12                 |
| 3         | 2016-03-02 | 0                  | 
| 3         | 2018-03-07 | 5                  |
+-----------+------------+--------------------+