550 Game Play Analysis Iv

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 fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.

The result format is in the following example.

Example 1:

Input: Activity table:

Output:

Explanation:

• Only the player with id 1 logged back in after the first day he had logged in so the answer is 1/3 = 0.33

Solution

Approach 1: Using Window Functions

The problem requires us to first find the first login dates. Once we have that, you can use window function to find the next login date using LEAD function. Next, you check if the difference between these two dates is 1 day or not. You count these players and divide it by the total number of unique players.

Find the consecutive logins

In order to find consecutive logins, we can use LEAD function. However, using only LEAD function wlll return records which occur in the consecutive dates. Even if we check if the two dates have a difference of 1 day, it will still return the result if there are two consecutive dates after the first login. For example, user 1 first login on 2016-03-01 and then does not login on 2016-03-02 but logs back in on 2016-03-05 and 2016-03-06. In this case, it will return the record with 2016-03-05 as it has consecutive logins on that date, but it is not the first login.

In order to get only first logins, you can use ROW_NUMBER function to get the row number for each player. You can use that to filter the result.

1WITH logins AS (
2    SELECT player_id, event_date, 
3        LEAD(event_date, 1) OVER (PARTITION BY player_id ORDER BY event_date) AS next_login_date,
4        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS row_num
5        FROM Activity
6) SELECT * FROM logins WHERE DATEDIFF(next_login_date, event_date) = 1 AND row_num = 1;

Calculate fraction

Once you have all player_ids which had consecutive logins, you can calculate the fraction. The total distinct players can be found using this query.

1SELECT COUNT(DISTINCT player_id) FROM Activity

Similarly, the total players with consecutive login after the first login can be found using below query based on logins view created above.

1SELECT COUNT(DISTINCT player_id) FROM logins WHERE DATEDIFF(next_login_date, event_date) = 1 AND row_num = 1

The last step is to divide the number of players with consecutive logins by the total number of players and round it to 2 decimal places.

The final solution will look like below.

 1WITH logins AS (
 2    SELECT player_id, event_date, 
 3        LEAD(event_date, 1) OVER (PARTITION BY player_id ORDER BY event_date) AS next_login_date,
 4        ROW_NUMBER() OVER (PARTITION BY player_id ORDER BY event_date) AS row_num
 5        FROM Activity
 6) SELECT 
 7    ROUND(
 8        SUM(
 9            (SELECT COUNT(DISTINCT player_id) FROM logins WHERE DATEDIFF(next_login_date, event_date) = 1 AND row_num = 1) / 
10            (SELECT COUNT(DISTINCT player_id) FROM Activity))
11    , 2) AS fraction

Approach 2: Using Aggreations

In this approch, you first need to find player first logins. This you can find using MIN() function on event_date column.

1WITH first_logins AS (
2    SELECT player_id, MIN(event_date) AS first_login_date 
3        FROM Activity GROUP BY player_id
4) SELECT * FROM first_logins;

Next, you can join this view with the original Activity table based on condition that both first_logins.player_id = Activity.player_id and DATEDIFF(Activity.event_date, first_logins.first_login_date) = 1. This will give you the players which had consecutive logins.

1logins AS (
2    SELECT COUNT(a.player_id) AS num_logins FROM first_logins a, Activity b
3        WHERE a.player_id = b.player_id AND DATEDIFF(b.event_date, a.first_login_date) = 1
4) SELECT * FROM logins;

The last step is to divide the number of players with consecutive logins by the total number of players and round it to 2 decimal places.

 1WITH first_logins AS (
 2    SELECT player_id, MIN(event_date) AS first_login_date 
 3        FROM Activity GROUP BY player_id
 4),
 5logins AS (
 6    SELECT COUNT(a.player_id) AS num_logins FROM first_logins a, Activity b
 7        WHERE a.player_id = b.player_id AND DATEDIFF(b.event_date, a.first_login_date) = 1
 8)
 9SELECT ROUND(
10    (SELECT num_logins FROM logins) /
11    (SELECT COUNT(player_id) FROM first_logins), 2
12) AS fraction;