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
LeetCode Database - Hard
LeetCode 185
LeetCode 262
LeetCode 569
LeetCode 571
LeetCode 579
LeetCode 601
LeetCode 615
LeetCode 618
LeetCode 1097
LeetCode 1127
LeetCode 1159
LeetCode 1194
LeetCode 1225
LeetCode 1336
LeetCode 1369
LeetCode 1384
LeetCode 1412
LeetCode 1479
LeetCode 1635
LeetCode 1645
LeetCode 1651
LeetCode 1767
LeetCode 1892
LeetCode 1917
LeetCode 1919
LeetCode 1972
More LeetCode Database
MySQL Tutorials