Game Play Analysis II

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 (combination of columns 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 the device that is first logged in for each player.

Example

Output

Try It Yourself

Solution

To identify the first device each player logged in with, we can leverage SQL's window functions, specifically FIRST_VALUE(). This function allows us to retrieve the first occurrence of a value within a specified window partition. By partitioning the data by player_id and ordering it by event_date, we can effectively determine the initial device used by each player.

• Partition Data by Player: Group the records based on player_id to analyze each player's activity individually.
• Order Events Chronologically: Within each player group, order the records by event_date to identify the sequence of device usage.
• Retrieve the First Device: Use the FIRST_VALUE() window function to extract the device ID from the earliest event for each player.
• Eliminate Duplicate Entries: Apply DISTINCT to ensure each player appears only once in the final result.

SQL Query

SELECT DISTINCT player_id,
                FIRST_VALUE(device_id) OVER (
                    PARTITION BY player_id
                    ORDER BY event_date
                ) AS device_id
FROM Activity;

Step-by-Step Approach

Step 1: Partition Data by Player and Order by Event Date

Group the activity records by player_id and order each group chronologically based on event_date to prepare for identifying the first device used.

SQL Query:

SELECT player_id,
       device_id,
       event_date,
       FIRST_VALUE(device_id) OVER (
           PARTITION BY player_id
           ORDER BY event_date
       ) AS first_device
FROM Activity;

Explanation:

• SELECT player_id, device_id, event_date, FIRST_VALUE(device_id) OVER (...) AS first_device:
  • Retrieves each player's ID, the device used, the date of the event, and the first device used by the player.
• FIRST_VALUE(device_id) OVER (PARTITION BY player_id ORDER BY event_date) AS first_device:
  • The FIRST_VALUE() function scans each partition (grouped by player_id) and retrieves the device_id from the earliest event_date.
• FROM Activity:
  • Indicates that the data is sourced from the Activity table.

Output After Step 1:

Based on the example input, the intermediate result would be:

+-----------+-----------+-------------+-------------+
| player_id | device_id | event_date  | first_device|
+-----------+-----------+-------------+-------------+
| 1         | 2         | 2016-03-01  | 2           |
| 1         | 2         | 2016-05-02  | 2           |
| 2         | 3         | 2016-03-01  | 3           |
| 3         | 1         | 2016-03-01  | 1           |
| 3         | 4         | 2018-07-03  | 1           |
+-----------+-----------+-------------+-------------+

Step 2: Select Distinct Player IDs with Their First Device

Extract each player's ID along with their first device, ensuring that each player appears only once in the final result.

SQL Query:

SELECT DISTINCT player_id,
                FIRST_VALUE(device_id) OVER (
                    PARTITION BY player_id
                    ORDER BY event_date
                ) AS device_id
FROM Activity;

Explanation:

• SELECT DISTINCT player_id, FIRST_VALUE(device_id) OVER (...) AS device_id:
  • Selects each unique player_id and associates it with the first device_id determined by the window function.

Final Output:

+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
| 1         | 2         |
| 2         | 3         |
| 3         | 1         |
+-----------+-----------+