盐咸咸

2022-06-19 13:47 已编辑数据分析师

关注

题解 | #2021年11月每天新用户的次日留存率#

2021年11月每天新用户的次日留存率

http://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450

解题步骤

part1：题目基本信息

1）统计2021年11月每天新用户的次日留存率（保留2位小数）

date_format(时间，‘%Y%m’)=‘202111’

2）次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。

换成容易理解的话就是，新用户的次留率。

3）如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序，没有新用户，不用输出。

这里可以理解为把out_time也算作是活跃日。

part2：解法1

基本的思路是：用union把in_time和out_time并联起来，对uid和date去重活获得一张用户活跃表。之后找出次活用户和活跃用户，计算留存率即可。

不要忘了几个基本条件，没有新用户不用输出，留存率保留2位小数，结果按日期升序，时间范围为2021年11月。

1）关联in_time和out_time字段,建立用户活跃表

SELECT DISTINCT uid,DATE(in_time) AS dt FROM tb_user_log
UNION
SELECT DISTINCT uid,DATE(out_time) AS dt FROM tb_user_log

2)找出新用户和次活新用户，并用‘1’来定义。

找出次活新用户

CASE WHEN (uid,dt)IN (SELECT uid,date_add(dt,INTERVAL -1 DAY) FROM t1) AND (uid,dt)IN (SELECT uid,MIN(dt)FROM t1 GROUP BY uid)
THEN 1 ELSE 0 END AS is_next_day
#当用户满足条件“连续两天登录（找次活用户），且第一个登录日期为首次登录日（找新用户）”，则整个用户为“次活新用户”

找出新用户

CASE WHEN (uid,dt) IN (SELECT uid,MIN(dt)FROM t1 GROUP BY uid)
THEN 1 ELSE 0 END is_new_day
#找出新用户

构建一张判断次活用户和新用户的表

WITH t1 AS(
SELECT DISTINCT uid,DATE(in_time) AS dt FROM tb_user_log
UNION
SELECT DISTINCT uid,DATE(out_time) AS dt FROM tb_user_log
)
SELECT dt,
		CASE WHEN (uid,dt)IN (SELECT uid,date_add(dt,INTERVAL -1 DAY) FROM t1) AND (uid,dt)IN (SELECT uid,MIN(dt)FROM t1 GROUP BY uid)
		THEN 1 ELSE 0 END AS is_next_day ,#次活新用户
		CASE WHEN (uid,dt) IN (SELECT uid,MIN(dt)FROM t1 GROUP BY uid)#次活用户
		THEN 1 ELSE 0 END is_new_day
FROM t1
WHERE DATE_FORMAT(dt,'%Y%m')='202111';#取2021年11月的数据

3）计算每日的次活用户数和新用户数，并求新用户次日留存率。

每日次活用户数：SUM(is_next_day)
每日新用户数：SUM(is_new_day)
次日留存率，保留2位小数ROUND(SUM(is_next_day)/SUM(is_new_day),2)
把is_next_day和 is_new_day的定义放进上一个代码结果如下

WITH t1 AS(
SELECT DISTINCT uid,DATE(in_time) AS dt FROM tb_user_log
UNION
SELECT DISTINCT uid,DATE(out_time) AS dt FROM tb_user_log
)#用户活跃表

SELECT dt,
		ROUND(SUM(CASE WHEN (uid,dt)IN (SELECT uid,date_add(dt,INTERVAL -1 DAY) FROM t1) AND (uid,dt)IN (SELECT uid,MIN(dt) FROM t1 GROUP BY uid) THEN 1 ELSE 0 END) 
              /
           SUM(CASE WHEN (uid,dt) IN (SELECT uid,MIN(dt)FROM t1 GROUP BY uid) THEN 1 ELSE 0 END),2) AS  uv_left_rate
FROM t1
WHERE DATE_FORMAT(dt,'%Y%m')='202111'
GROUP BY dt
HAVING uv_left_rate IS NOT NULL
ORDER BY dt;

PART3:解法2窗口函数解法

1）照例找出用户活跃表

SELECT DISTINCT uid,DATE(in_time) AS dt FROM tb_user_log
UNION
SELECT DISTINCT uid,DATE(out_time) AS dt FROM tb_user_log

2）找出次活日期和首次登录日期，并判断新用户和次留新用户。

通过位移窗口函数对每个用户的活跃日向上迁移一行

SELECT uid,dt,
	MIN(dt) OVER (PARTITION BY uid) AS new_dt,
    LEAD(dt,1) OVER (PARTITION BY uid ORDER BY dt) AS next_dt
       
FROM(
	SELECT DISTINCT uid,DATE(in_time) AS dt FROM tb_user_log
	UNION
	SELECT DISTINCT uid,DATE(out_time) AS dt FROM tb_user_log
)act_table
WHERE DATE_FORMAT(dt,'%Y%m')='202111'

如果dt=new_dt那这个用户为新用户，如果dt=new_dt且next_dt和new_dt的日期差为1则这个用户为次留新用户

WITH t1 AS(
SELECT uid,dt,
	MIN(dt) OVER (PARTITION BY uid) AS new_dt,
    LEAD(dt,1) OVER (PARTITION BY uid ORDER BY dt) AS next_dt
       
FROM(
	SELECT DISTINCT uid,DATE(in_time) AS dt FROM tb_user_log
	UNION
	SELECT DISTINCT uid,DATE(out_time) AS dt FROM tb_user_log
) act_table
WHERE DATE_FORMAT(dt,'%Y%m')='202111'
)
SELECT dt, 
	CASE WHEN dt=new_dt AND datediff(next_dt,dt)=1 THEN 1 ELSE 0 END is_next_day,
    CASE WHEN dt=new_dt THEN 1 ELSE 0 END is_new_day
FROM t1;

3）计算新用户次留率

WITH t1 AS(
SELECT uid,dt,
	MIN(dt) OVER (PARTITION BY uid) AS new_dt,
    LEAD(dt,1) OVER (PARTITION BY uid ORDER BY dt) AS next_dt
       
FROM(
	SELECT DISTINCT uid,DATE(in_time) AS dt FROM tb_user_log
	UNION
	SELECT DISTINCT uid,DATE(out_time) AS dt FROM tb_user_log
) act_table
WHERE DATE_FORMAT(dt,'%Y-%m')='2021-11'
)

SELECT dt, 
	ROUND(SUM(CASE WHEN dt=new_dt AND datediff(next_dt,dt)=1 THEN 1 ELSE 0 END)
	      /
	      SUM(CASE WHEN dt=new_dt THEN 1 ELSE 0 END),2) uv_left_rate
FROM t1
GROUP BY dt
HAVING uv_left_rate IS NOT NULL
ORDER BY dt;

SQL解题集文章被收录于专栏

这是牛客SQL相关的解题集

全部评论

推荐最新楼层

红烧牛客

数据分析师

法2答案的调整，私信发你了，注意查收！

送花回复

发布于 2022-06-17 18:19

Forwardsummer

数据其它

第二种方法没过的，试试下面的方法，和楼主的第二种方法一样的思想，纯窗口函数： select dt,round(sum(if(timestampdiff(day,dt,lead_time)=1,1,0))/count(*),2) from( select *, row_number() over(partition by uid order by dt) rk, lead(dt,1) over(partition by uid order by dt) lead_time from( select uid,date(in_time) as dt from tb_user_log union select uid,date(out_time) as dt from tb_user_log )t1 )t2 where rk = 1 group by dt having date_format(dt,'%Y-%m') = '2021-11' order by dt 这里引用Cole4Youreyez用户的结论： “该题有一个易错点，那就是题目中第一句话就说明了在十一月份的，可能做题人会考虑在建立表t时就直接where语句选出11月的，这样是错误的。原因在于：在建立表t是where会先运行，也就是选择了11月的信息再进行排序。那么如果一个用户10月活跃过，在11月中，11月2号和3号活跃了，这样的操作会使得该用户11-02的排名rk=1，也会被我们认为是新用户，但是显然并非如此。”

送花回复

发布于 2023-02-19 20:32 江苏

秋招专场

校招火热招聘中

官网直投

牛客318150745号

运营

想了很久都理解不了，为什么不是INTERVAL 1 DAY【CASE WHEN (uid,dt)IN (SELECT uid,date_add(dt,INTERVAL -1 DAY) FROM t1) AND (uid,dt)IN (SELECT uid,MIN(dt)FROM t1 GROUP BY uid) THEN 1 ELSE 0 END AS is_next_day #当用户满足条件“连续两天登录（找次活用户），且第一个登录日期为首次登录日（找新用户）”，则整个用户为“次活新用户”】既然都第一次登录了，那不应该和第二天（+1）的比吗

送花回复

发布于 2022-06-01 19:13

Forwardsummer

数据其它

使用having就能避免先where过滤掉的情况，即该用户在10月活跃过，时间来到11月，他不是新用户

送花回复

发布于 2023-02-19 20:33 江苏

D_gan

深圳大学数学类

WHERE DATE_FORMAT(dt,'%Y%m')='202111' -> WHERE DATE_FORMAT(new_dt,'%Y%m')='202111' 当然，这样改后要换个地方放

送花回复

发布于 2022-04-22 19:56

Wailliams

数据分析师

方法2是不是答案无法过检测哇

送花回复

发布于 2022-08-16 13:16

琳波微步

数据分析师

子查询用的妙呀

送花回复

发布于 2022-12-20 09:28 北京

忄喵喵喵

数据分析师

如果有人在第1天进入，持续到第3天才离开，且之后的日期也有活跃，只用datediff(next_dt,dt)=1就没法包含这种情况了，直接>=也不行，应该怎么写才能把这种情况也含进去呀

送花回复

发布于 2023-02-09 20:35 湖北

我说我不帅她们就打我还说我很虚伪

大数据开发工程师

第二种窗口函数方法中的第11行sql中：where限定的有问题，因为这样会把11月份之前的老用户当成新用户充当分母计算留存率；第15行sql中的datediff(next_dt,dt)=1限定也有点小问题，因为存在持续超过2天一直挂着的用户-个人觉得这种情况应该考虑的，但是介于已经把out_time和in_time都转成一行了，这种情况目前看了用这个sql解决不了的。sql改一下可以通过： WITH t1 AS( select uid,dt,new_dt,next_dt from( SELECT uid,dt, MIN(dt) OVER (PARTITION BY uid) AS new_dt, LEAD(dt,1) OVER (PARTITION BY uid ORDER BY dt) AS next_dt FROM( SELECT DISTINCT uid,DATE(in_time) AS dt FROM tb_user_log UNION SELECT DISTINCT uid,DATE(out_time) AS dt FROM tb_user_log ) act_table #WHERE DATE_FORMAT(dt,'%Y-%m')='2021-11' ) t1 where datediff(new_dt,'2021-11-01')>=0 ) SELECT dt, ROUND(SUM(CASE WHEN dt=new_dt AND datediff(next_dt,dt)=1 THEN 1 ELSE 0 END) / SUM(CASE WHEN dt=new_dt THEN 1 ELSE 0 END),2) uv_left_rate FROM t1 GROUP BY dt HAVING uv_left_rate IS NOT NULL ORDER BY dt;

送花回复

发布于 2023-08-08 12:04 上海

牛客356180146号

山东大学数学类

为啥DATEDIFF(next_dt,dt) = 1而不是DATEDIFF(dt,next_dt) = 1呢？后者的计算结果全为0。。。但是DATEDIFF(start_date, end_date)不是开始日期在前结束日期在后吗？

送花回复

发布于 2023-09-15 10:13 山东

Aron_SUN

香港城市大学图书情报与档案管理类

方案2应该有问题因为from的数据是活跃数据不一定是新用户

送花回复

发布于 03-04 14:37 香港