有点难度的SQL题---计算用户的平均次日留存率

SQL 计算用户的平均次日留存率

目录

需求描述:

现在相关部门想要查看,用户在某一天刷题后,第二天还会来刷题的平均概率,即次日留存率

数据示例:

表 question_practice_detail
《有点难度的SQL题---计算用户的平均次日留存率》
根据示例,看到表question_practice_detail中,有5个字段,分别是:

  • id 自增id
  • device_id 设备号 标识唯一用户
  • question_id 问题id
  • result 问题的结果
  • date 登录时间

查询结果

avg_ret 代表平均次日留存率

《有点难度的SQL题---计算用户的平均次日留存率》

问题分析

1. 次日留存

构建一个新的列

  • 使用lead()over()函数,构造新的时间列,作为下次登录时间 new_date
  • lead函数中,需要对设备号进行分组,时间升序排序
  • 即 LEAD(DATE,1)OVER(PARTITION BY device_id ORDER BY DATE)

如何判断一个人第一天刷题后,第二天还刷题了?

  • 利用时间函数 datediff(new_date,date)
  • 代表 new_date – date , 暂且把这个值 定为rn
  • 如果rn=1,那么就代表相隔一天,用户重新上线刷题
  • 所以rn=1就是我们定位次日留存的入口

2. 平均值

平均值怎么算?

  • (第一天刷题,第二天还刷 的用户数) / (第一天刷题 的用户数) 即可

《有点难度的SQL题---计算用户的平均次日留存率》

我的代码

SELECT 
	SUM(IF(rn=1,1,0))/SUM(IF(rn!=0 OR rn IS NULL,1,0)) avg_ret
FROM 	
(SELECT 
	DISTINCT device_id,
	`date`,
	LEAD(DATE,1)OVER(PARTITION BY device_id ORDER BY DATE)  new_date,
	DATEDIFF(LEAD(DATE,1)OVER(PARTITION BY device_id ORDER BY DATE),DATE) rn
FROM 
	question_practice_detail)a;   
  

《有点难度的SQL题---计算用户的平均次日留存率》

解释一下,次日留存率 avg_ret最后的统计:
SUM(IF(rn=1,1,0)) / SUM(IF(rn!=0 OR rn IS NULL,1,0)) avg_ret

  • SUM(IF(rn=1,1,0)) 代表 满足次日留存的条目数,只要rn=1,就代表第二天这个人又来刷题了,值取1 ,如果rn !=1 就代表次日没来,值取0;

  • 统计 1 的个数 就是符合次日留存的 条目总数;

  • SUM(IF(rn!=0 OR rn IS NULL,1,0)) avg_ret

  • 其中 rn != 0 过滤掉 new_date = date的数据,明显是无意义的;(这里还有别的方法过滤,我试了几种,比如 最内层的表先distinct device_id,再查询,也就是说再嵌套一层,欢迎评论区的小伙伴指点,或者有更好的方法欢迎评论区留言

  • 由于 rn !=0 会自动将null过滤,但是我们也是需要null统计,因为null也没关系,加上rn IS NULL的值 ,才是完整的 第一天就刷题的人的 总条目数;

欢迎评论区指点

    原文作者:xxr_yl
    原文地址: https://blog.csdn.net/m0_57102326/article/details/122782588
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞