1097 Game Play Analysis V

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.

• The install date of a player is the first login day of that player.

We define day one retention of some date x to be the number of players whose install date is x and they logged back in on the day right after x, divided by the number of players whose install date is x, rounded to 2 decimal places.

Problem Statement

Write a solution to report for each install date, the number of players that installed the game on that day, and the day one retention.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input:

• Activity table:

Output:

Explanation:

• Player 1 and 3 installed the game on 2016-03-01 but only player 1 logged back in on 2016-03-02 so the day 1 retention of 2016-03-01 is 1 / 2 = 0.50
• Player 2 installed the game on 2017-06-25 but didn’t log back in on 2017-06-26 so the day 1 retention of 2017-06-25 is 0 / 1 = 0.00

Solution

The problem can be broken down into following parts.

1. You need to find number of installs, therefore you need to first find the install_date for each player or first_login_date for reach player.

In order to find the first_login, you can use MIN(event_date) by grouping on each player.

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

2. The next step is to find out the players who logged in on the next day after the install. This will help calculate the Day1_Retention.

You can get this by joining first_logins view with Activity table on the condition that player_id matches and the difference between event_date and first_login_date is 1. You will have to perform LEFT OUTER JOIN as we want only those results where the user logged in on the next day.

1first_logins F
2LEFT JOIN Activity A ON F.player_id = A.player_id
3AND F.first_login = DATE_SUB(A.event_date, INTERVAL 1 DAY)

To find the player logged in on the next day, you can check if the A.player_id is NULL. If it is NULL, that means the next event_date was not the consecutive day. This will eventually help us with Day1_Retention calculation.

 1consec_login_info AS (
 2    SELECT
 3        F.player_id,
 4        (CASE
 5            WHEN A.player_id IS NULL THEN 0
 6            ELSE 1
 7            END
 8        ) AS logged_in_consecutively,
 9        F.first_login
10        FROM first_logins F
11        LEFT JOIN Activity A ON F.player_id = A.player_id
12            AND F.first_login = DATE_SUB(A.event_date, INTERVAL 1 DAY)
13    ) SELECT * FROM consec_login_info;

Here is logged_in_consecutively column is acting as a boolean indicating whether the player logged in on the next day or not.

3. Next, calculate the Day1_Retention field. Based on above output, you can calculate those easily. In this case, you need to group the results by first_login_date field and calculate the SUM(logged_in_consecutively). You need to output the Day1_Retention which is SUM(logged_in_consecutively) / COUNT(player_id). The next step is to round the Day1_Retention to 2 decimal places.

 1SELECT
 2    C.first_login_date AS install_dt,
 3    COUNT(C.player_id) AS installs,
 4    ROUND(
 5        SUM(C.logged_in_consecutively)
 6        / COUNT(C.player_id)
 7    , 2) AS Day1_Retention
 8FROM
 9    consec_login_info C
10GROUP BY
11    C.first_login_date;

The final solution looks like this.

 1WITH first_logins AS (
 2    SELECT player_id,
 3    MIN(event_date) AS first_login_date
 4    FROM Activity 
 5    GROUP BY player_id
 6), consec_login_info AS (
 7    SELECT
 8        F.player_id,
 9        (CASE
10            WHEN A.player_id IS NULL THEN 0
11            ELSE 1
12            END
13        ) AS logged_in_consecutively,
14        F.first_login_date
15        FROM first_logins F
16        LEFT JOIN Activity A ON F.player_id = A.player_id
17            AND F.first_login_date = DATE_SUB(A.event_date, INTERVAL 1 DAY)
18) SELECT
19    C.first_login_date AS install_dt,
20    COUNT(C.player_id) AS installs,
21    ROUND(
22        SUM(C.logged_in_consecutively)
23        / COUNT(C.player_id)
24    , 2) AS Day1_Retention
25FROM
26    consec_login_info C
27GROUP BY
28    C.first_login_date;