Game Play Analysis IV Problem

1
2
3
4
5
6
7
8
9
10
11
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
(player_id, event_date) is the primary key of this table.
This table shows the activity of players of some game.
Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on some day using some device.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-03-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+

Result table:
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
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

1
2
3
4
5
6
7
8
9
select round(a_frac.playerCount / count(distinct a_full.player_id), 2) as fraction
from Activity a_full,
    (select count(distinct a1.player_id) as playerCount
    from Activity a1
    inner join
        (select player_id, min(event_date) as first_login
        from Activity 
        group by player_id) a2
    on a1.player_id = a2.player_id and datediff(a1.event_date, a2.first_login) = 1) a_frac