目录
1. 力扣链接
2. 题目
表：
Activity+--------------+---------+ | Column Name | Type | +--------------+---------+ | player_id | int | | device_id | int | | event_date | date | | games_played | int | +--------------+---------+ （player_id，event_date）是此表的主键(具有唯一值的列的组合) 这张表显示了某些游戏的玩家的活动情况 每一行表示一个玩家的记录，在某一天使用某个设备注销之前，登录并玩了很多游戏（可能是 0）玩家的 安装日期 定义为该玩家的第一个登录日。
我们将日期 x 的 第一天留存率 定义为：假定安装日期为
X的玩家的数量为N，其中在X之后的一天重新登录的玩家数量为M，M/N就是第一天留存率，四舍五入到小数点后两位。编写解决方案，报告所有安装日期、当天安装游戏的玩家数量和玩家的 第一天留存率。
以 任意顺序 返回结果表。
结果格式如下所示。
示例 1：
输入： 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 | +-----------+-----------+------------+--------------+ 输出： +------------+----------+----------------+ | install_dt | installs | Day1_retention | +------------+----------+----------------+ | 2016-03-01 | 2 | 0.50 | | 2017-06-25 | 1 | 0.00 | +------------+----------+----------------+ 解释： 玩家 1 和 3 在 2016-03-01 安装了游戏，但只有玩家 1 在 2016-03-02 重新登录，所以 2016-03-01 的第一天留存率是 1/2=0.50 玩家 2 在 2017-06-25 安装了游戏，但在 2017-06-26 没有重新登录，因此 2017-06-25 的第一天留存率为 0/1=0.00
-- 建表语句
Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int);
Truncate table Activity;
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5');
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-02', '6');
insert into Activity (player_id, device_id, event_date, games_played) values ('2', '3', '2017-06-25', '1');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-01', '0');
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5');3. 分析
确定安装日期：首先，我们需要确定每个玩家的安装日期，即他们第一次登录游戏的日期。
计算每天的新玩家数量：对于每个安装日期，我们需要计算有多少新玩家在那天首次登录。
计算留存玩家数量：对于每个安装日期，我们需要计算在安装日期的第二天登录的玩家数量。
计算留存率：使用留存玩家数量除以新玩家数量，得到留存率。
四舍五入：将留存率四舍五入到小数点后两位。
结果格式化：将安装日期、新玩家数量和留存率整合到一个结果表中。
4. 代码实现
select
install_dt,
count(tmp.player_id) installs,
round(count(event_date) / count(tmp.player_id), 2) Day1_retention
from
(
select
player_id,
# 求出首次安装时间
min(event_date) install_dt
from Activity
group by player_id
) tmp
left join Activity a
on tmp.player_id = a. player_id
and datediff(event_date,install_dt) = 1 -- 对次日登录关联
group by install_dt5. 代码验证
6. 总结
- 左连接：用于将首次登录信息与原始活动记录连接，以识别次日活跃玩家。
- 日期差异计算：使用DATEDIFF函数来计算两个日期之间的差异，确保比较的是安装日期的次日活动。
- 条件计数：通过COUNT函数在LEFT JOIN的基础上计算次日活跃玩家的数量。
- 留存率计算：通过比较次日活跃玩家与总玩家数量来计算留存率。
- 四舍五入：使用ROUND函数对留存率结果进行格式化，保留两位小数。