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         |
+-----------+-----------+