本文共 3316 字，大约阅读时间需要 11 分钟。

1. 题目

Activity 活动记录表

+--------------+---------+| Column Name  | Type    |+--------------+---------+| player_id    | int     || device_id    | int     || event_date   | date    || games_played | int     |+--------------+---------+（player_id，event_date）是此表的主键这张表显示了某些游戏的玩家的活动情况每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）

我们将玩家的安装日期定义为该玩家的第一个登录日。

我们还将某个日期 X 的第 1 天留存时间定义为安装日期为 X 的玩家的数量，他们在 X 之后的一天重新登录，除以安装日期为 X 的玩家的数量，四舍五入到小数点后两位。

编写一个 SQL 查询，报告每个安装日期、当天安装游戏的玩家数量和第一天的留存时间。

查询结果格式如下所示：

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           |+------------+----------+----------------+玩家 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

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/game-play-analysis-v

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

2. 解题

• 先找出安装日期，和安装数量

select distinct install_dt,         count(*) over(partition by install_dt) installsfrom(    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_loginfrom Activity awhere(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 belowwith 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_retentionfrom t left join(    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) t1on 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_retentionfrom(    select player_id, min(event_date) install_dt    from Activity    group by player_id) a1left join Activity a2on a1.player_id = a2.player_id and datediff(a2.event_date, a1.install_dt)=1group by a1.install_dt

我的CSDN

长按或扫码关注我的公众号（Michael阿明），一起加油、一起学习进步！

转载地址：https://michael.blog.csdn.net/article/details/107729091 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！