【MySQL】LeetCode 1097
2020-02-20 本文已影响0人
每天要读书的Claire
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)
