# LeetCode MySQL 1097. 游戏玩法分析 V

## 1. 题目

Activity 活动记录表

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
（player_id，event_date）是此表的主键



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         | 2016-07-03 | 5            |
+-----------+-----------+------------+--------------+

Result 表：
+------------+----------+----------------+
| install_dt | installs | Day1_retention |
+------------+----------+----------------+
| 2016-03-01 | 2        | 0.50           |
| 2017-06-25 | 1        | 0.00           |
+------------+----------+----------------+



## 2. 解题

• 先找出安装日期，和安装数量
select distinct install_dt,
count(*) over(partition by install_dt) installs
from
(
select player_id, min(event_date) install_dt
from Activity
group by player_id
) temp

{"headers": ["install_dt", "installs"],
"values": [["2016-03-01", 2],
["2017-06-25", 1]]}

• 第二天还登录的数量
select a.event_date, count(*) second_login
from Activity a
where(a.player_id, date_sub(a.event_date, interval 1 day))
in
(
select player_id, min(event_date) install_dt
from Activity
group by player_id
)
group by a.event_date

{"headers": ["event_date", "second_login"],
"values": [["2016-03-02", 1]]}

• 连接后，两指标相除
# Write your MySQL query statement below
with t as
(
select distinct install_dt,
count(*) over(partition by install_dt) installs
from
(
select player_id, min(event_date) install_dt
from Activity
group by player_id
) temp
)

select install_dt, installs, round(ifnull(second_login,0)/installs, 2) Day1_retention
from
t left join
(
from Activity a
where(a.player_id, date_sub(a.event_date, interval 1 day))
in
(
select player_id, min(event_date) install_dt
from Activity
group by player_id
)
group by a.event_date
) t1
on t.install_dt = date_sub(t1.event_date, interval 1 day)
group by install_dt

• 评论区简洁答案
select a1.install_dt,
count(*) installs,
round(count(a2.event_date)/count(*),2) Day1_retention
from
(
select player_id, min(event_date) install_dt
from Activity
group by player_id
) a1
left join Activity a2
on a1.player_id = a2.player_id and datediff(a2.event_date, a1.install_dt)=1
group by a1.install_dt


02-04 269

04-20 60
02-23 85
03-24 62
02-20 140