题解 | #计算用户的平均次日留存率#

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

http://www.nowcoder.com/practice/126083961ae0415fbde061d7ebbde453

题意明确:

用户在某天刷题后第二天再来刷题的平均概率


问题分解:

  • 限定条件:第二天再来。
    • 解法1:表里的数据可以看作是全部第一天来刷题了的,那么我们需要构造出第二天来了的字段,因此可以考虑用left join把第二天来了的拼起来,限定第二天来了的可以用date_add(date1, interval 1 day)=date2筛选,并用device_id限定是同一个用户。
    • 解法2:用lead函数将同一用户连续两天的记录拼接起来。先按用户分组partition by device_id,再按日期升序排序order by date,再两两拼接(最后一个默认和null拼接),即lead(date) over (partition by device_id order by date)
  • 平均概率:
    • 解法1:可以count(date1)得到左表全部的date记录数作为分母,count(date2)得到右表关联上了的date记录数作为分子,相除即可得到平均概率
    • 解法2:检查date2和date1的日期差是不是为1,是则为1(次日留存了),否则为0(次日未留存),取avg即可得平均概率。
  • 附:lead用法date_add用法datediff用法date函数

细节问题:

  • 表头重命名:as
  • 去重:需要按照devece_id,date去重,因为一个人一天可能来多次
  • 子查询必须全部有重命名

完整代码:

select count(date2) / count(date1) as avg_ret
from (
    select
        distinct qpd.device_id,
        qpd.date as date1,
        uniq_id_date.date as date2
    from question_practice_detail as qpd
    left join(
        select distinct device_id, date
        from question_practice_detail
    ) as uniq_id_date
    on qpd.device_id=uniq_id_date.device_id
        and date_add(qpd.date, interval 1 day)=uniq_id_date.date
) as id_last_next_date

解法2:

select avg(if(datediff(date2, date1)=1, 1, 0)) as avg_ret
from (
    select
        distinct device_id,
        date as date1,
        lead(date) over (partition by device_id order by date) as date2
    from (
        select distinct device_id, date
        from question_practice_detail
    ) as uniq_id_date
) as id_last_next_date
全部评论
请问一下,select count(date1)/count(date0) as avg_ret from ( select distinct date(t.date) as date0, t.device_id, t1.date as date1 from question_practice_detail as t left join question_practice_detail as t1 on t1.device_id=t.device_id and datediff(date(t1.date),date(t.date))=1 order by date0 ) as a 这样的作法提交也是正确的,但是否只是巧合有漏洞?
1 回复 分享
发布于 2022-02-25 20:12
困难题,竟恐怖如斯
9 回复 分享
发布于 2022-05-13 09:16
解法1的计算公式不对,应该是count(date2)/count(distinct device_id)
4 回复 分享
发布于 2022-03-03 11:37
好厉害理解了楼主,不过发现子查询后面都要重新as,感觉好像子查询那里的括号不对👍👍👍
3 回复 分享
发布于 2022-05-22 21:04
想请教一下,解法二中为啥必须要加第二个子查询,第二个子查询为什么不能省去,直接用from question_answer_detail,这里是不是和distinct的顺序有关
3 回复 分享
发布于 2022-04-18 23:21
里面的子查询看麻了
3 回复 分享
发布于 2022-01-29 10:18
看不懂先收藏
3 回复 分享
发布于 2022-01-26 19:16
avg(if(datediff(date2, date1)=1, 1, 0)) as avg_ret,这个得到的不是1吗?没理解其中逻辑。
3 回复 分享
发布于 2021-12-16 00:19
请问怎么做到的按id和日期去重的?
3 回复 分享
发布于 2021-11-11 14:18
left join里面的distinct可以省略,外面有一次distinct就行
2 回复 分享
发布于 2022-07-15 15:01
第二种解法 select distinct device_id, date from question_practice_detail 可以换成用 group by。SELECT AVG(IF(DATEDIFF(date2, `date`)=1, 1, 0)) avg_ret FROM ( SELECT device_id, `date`, lead(`date`, 1, '0') OVER(PARTITION BY device_id ORDER BY `date`) date2 FROM question_practice_detail GROUP BY device_id, `date` ) T
2 回复 分享
发布于 2022-07-08 00:07
为什么要用左连接
2 回复 分享
发布于 2022-06-21 22:22
实在不行就把大佬写的sql分解开来就做查询测试就能明白的快一些
2 回复 分享
发布于 2022-04-11 19:38
看是能看懂 然后又有问题发现还是用不出来
2 回复 分享
发布于 2022-02-16 16:06
解法2里为什么要加入select distinct device_id, date from question_practice_detail这个表, 我在MySQL里 select distinct device_id, date as date1, lead(date) over (partition by device_id order by date) as date2 from ( select distinct device_id, date from question_practice_detail ) as uniq_id_date 和 select distinct device_id, date as date1, -> lead(date,1,0) over (partition by device_id order by date)as date2 -> from question_practice_detail; 得出来的表格是一样的,不影响计算平均值吧 但是为什么我提交显示是错误呢
2 回复 分享
发布于 2022-01-25 08:59
第二种解法,得到的不是平均概率吧
2 回复 分享
发布于 2021-11-24 19:53
感觉这个不能叫平均用户留存率,就是整个表的用户留存率。。没有体现平均,理解平均是每个用户的留存率的平均,但这里是指整个表的留存用户/用户,不就是用户留存率而已嘛
1 回复 分享
发布于 2024-04-18 18:14 湖南
先关心解法1,解法1用了自查询,自查询left join 后面再on会继续筛选笛卡尔积的表,on条件是id相同, 因为左表有多个id,所以会变成id数量积,比如原来5个id,会变成25行数据,所以会出现多条数据, 继续and筛选,DATE_ADD(qpd.date, INTERVAL 1 DAY)=uniq_id_date.date d2比d1多一天的数据, 与 DATEDIFF(uniq_id_date.date,qpd.date)=1 效果一致;最终再(),用count求概率。
1 回复 分享
发布于 2023-06-29 14:08 山东
2023.3.20 直接用大佬的代码居然报错了 这是为啥
1 回复 分享
发布于 2023-03-30 15:33 浙江
第二个解法的第一个DISTINCT可以删掉,因为在最里面一层的SELECT已经有DISTINCT了
1 回复 分享
发布于 2022-11-06 19:56 湖北

相关推荐

Cherrycola01:0实习 0项目 约等于啥也没有啊 哥们儿这简历认真的吗
点赞 评论 收藏
分享
评论
738
249
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务