512 Game Play Analysis II

Description

Table: Activity

• (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 Statement

Write a solution to report the device that is first logged in for each player. Return the result table in any order. The result format is in the following example.

Example 1:

Input: Activity table:

Output:

Solution

There are couple of approaches to solve this problem

Approach 1: Using Window Functions

This problem requires the oldest value when ordered by event_date for each player_id. This can be solved by creating a window for each player and ordering by event_date and finding the first value in the window. The first value can be obtained by assigning incremental row number to reach row using ROW_NUMBER() window function while getting only rows with row=1.

1WITH cte AS (
2    SELECT player_id, device_id, 
3        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS 'row_num' 
4        FROM Activity
5) 
6SELECT player_id, device_id FROM cte WHERE row_num = 1 ORDER BY player_id;

Approach 2: using MIN() function.

Another option is to find the rows with minimum value for event_date grouped by player_id. This will return the oldest event for each player. Next, you can filter the rows from Activity table where player_id and event_date matches the result of previous query. In this query, you select only player_id and device_id as those are the only fields required in the output.

1SELECT player_id, device_id FROM Activity
2    WHERE (player_id, event_date) IN (
3        SELECT player_id, MIN(event_date) AS event_date FROM Activity
4        GROUP BY player_id
5        ORDER BY player_id
6    );