115，[经典面试题--留存率计算]SQL训练之，力扣，1097. 游戏玩法分析 V

最新推荐文章于 2024-10-05 14:08:26 发布

凡梦_leo

最新推荐文章于 2024-10-05 14:08:26 发布

阅读量690

点赞数 18

分类专栏： SQL实战训练文章标签： leetcode pandas python 数据库 sql 留存率新增率

本文链接：https://blog.csdn.net/qq_55006020/article/details/142713756

版权

SQL实战训练专栏收录该内容

117 篇文章 0 订阅

订阅专栏

学习：知识的初次邂逅
复习：知识的温故知新
练习：知识的实践应用

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

表：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');

四，分析

计算首日留存率: 表格大法: MYSQL用的是一种方法:

第0步:去重,去除同一个玩家用同一个id在同一天多次登陆的情况;

第一步:以用户分组,计算每个用户的首次登陆日期

第二步:计算用户首日活跃用户次数;以首次登陆日期分组,聚合用户id

第三步:开一个新列,计算首日登陆日期的下一天日期,也就是次日的日期;

第四步:计算次日活跃用户数量, 条件是该用户首次登陆日期,和首日登陆日期+1天都登陆过; 第五步:合并首日活跃用户表和次日活跃用户表

第六步:计算留存率留存率 = 次日活跃用户表的用户数量 / 首日活跃用户表的用户数

第七步:映射指定的列,改名,并且四舍五入输出: 详细实现见表格+代码

第0步:去重,去除同一个玩家用同一个id在同一天多次登陆的情况;

第一步:以用户分组,计算每个用户的首次登陆日期

第二步:计算用户首日活跃用户次数;以首次登陆日期分组,聚合用户id

第三步:开一个新列,计算首日登陆日期的下一天日期,也就是次日的日期;

第四步:计算次日活跃用户数量, 条件是该用户首次登陆日期,和首日登陆日期+1天都登陆过;

第五步:合并首日活跃用户表和次日活跃用户表

第六步:计算留存率留存率 = 次日活跃用户表的用户数量 / 首日活跃用户表的用户数

第七步:映射指定的列,改名,并且四舍五入输出:

详细实现见表格+代码

五，SQL解答


with t0 as (  #去重
    select distinct player_id,device_id,event_date from Activity
)
#  select * from t0;
   , t1 as (
    select
    player_id, device_id, event_date,
    min(event_date)over(partition by player_id order by event_date) mo
    from t0
)
#    select * from t1;
,t2 as (
    #计算第一天登陆的活跃玩家 数量
    select
        event_date,count(player_id) as cnt1
    from t1 where event_date=mo group by event_date
)
# select * from t2;
   ,t3 as (
    select
    player_id, device_id, event_date,mo,
    date_add(mo,interval 1 day) ciri2
    from t1
)
#    select * from t3;
   ,t4 as (
    #计算第二天登陆的活跃玩家 数量
select event_date,count(player_id) cnt2 from t3 where event_date =ciri2 group by event_date
)
#    select * from t4;
,t5 as (
    select t2.event_date,cnt1,ifnull(cnt2,0)  as cnt2 from t2 
   left join t4 
   on t2.event_date= date_add(t4.event_date,interval -1 day)
)
select event_date as install_dt,cnt1 as installs ,
    round(cnt2/cnt1,2) Day1_retention
from t5;

六，验证

七，知识点总结

留存率问题比如一日留存率
次日留存率 = 次日登陆用户数量/首日登录用户数量; 且次日活跃用户在第一日也有登陆过;
次日新增率=次日新增用户数量/首日新增用户数量; 且次日用户在第一日没有登陆过;
次日流失率 = 次日流失的用户/ 第一天的用户总数 ×100%。次日流失的用户是指,在第一天有登陆,但是在第二天没有登陆过;
或者表格来看: