【MySQL】LeetCode 1097

1097 游戏玩法分析5

mysql> select * from activity;
+-----------+-----------+------------+--------------+
| 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 | 2018-07-03 |            5 |
|         1 |         2 | 2016-08-03 |            4 |
+-----------+-----------+------------+--------------+
6 rows in set (0.01 sec)

求每天(若有)的 new install数 以及 次日留存率
select install_date,
count(player_id)as installs ,
count(event_date)/count(player_id) as Day1_retention 
from 
(select T1.player_id,
T1.install_date,
T2.event_date 
from (select player_id,
min(event_date) as install_date 
from activity
group by player_id) T1
left join activity T2
on T1.player_id=T2.player_id
and T1.install_date+1=T2.event_date)A
group by install_date ;

+--------------+----------+----------------+
| install_date | installs | Day1_retention |
+--------------+----------+----------------+
| 2016-03-01   |        2 |         0.5000 |
| 2017-06-25   |        1 |         0.0000 |
+--------------+----------+----------------+
2 rows in set (0.00 sec)