Game Play Analysis V Problem


Description

LeetCode Problem 1097.

Table: Activity

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.

We define the install date of a player to be the first login day of that player.

We also define day 1 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.

Write an SQL query that reports for each install date, the number of players that installed the game on that day and the day 1 retention.

The query result format is in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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-01 | 0            |
| 3         | 4         | 2016-07-03 | 5            |
+-----------+-----------+------------+--------------+

Result table:
+------------+----------+----------------+
| install_dt | installs | Day1_retention |
+------------+----------+----------------+
| 2016-03-01 | 2        | 0.50           |
| 2017-06-25 | 1        | 0.00           |
+------------+----------+----------------+
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


MySQL Solution

1
2
3
4
5
6
7
8
9
10
11
12
select t1.install_date as install_dt, count(t1.install_date) as installs,
    round(count(t2.event_date) / count(*), 2) as Day1_retention
from (
    select player_id, min(event_date) as install_date
    from Activity
    group by 1
) t1
left join Activity t2 
on date_add(t1.install_date, interval 1 day) = t2.event_date
    and t1.player_id = t2.player_id
group by 1
order by 1




Related Posts

Game Play Analysis V Problem

LeetCode 1097. Write an SQL query that reports for each...

Unpopular Books Problem

LeetCode 1098. Write an SQL query that reports the books...

New Users Daily Count Problem

LeetCode 1107. Write an SQL query that reports for every...

Sales Analysis III Problem

LeetCode 1084. Write an SQL query that reports the products...

Sales Analysis II Problem

LeetCode 1083. Write an SQL query that reports the buyers...

Sales Analysis I Problem

LeetCode 1082. Write an SQL query that reports the best...