SQL练习68：牛客每个人最近的登录日期(三)

最新推荐文章于 2023-06-01 17:56:13 发布

原创最新推荐文章于 2023-06-01 17:56:13 发布 · 614 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #数据库

Mysql数据库实战题（每日打卡）专栏收录该内容

67 篇文章

订阅专栏

该博客介绍了如何使用SQL查询计算牛客网新登录用户的次日留存率。通过子查询获取用户首次登录后的第二天日期，然后在登录记录表中查找这些用户是否在次日再次登录。统计成功留存的用户数并除以总用户数，得到次日留存率，结果保留三位小数。示例中给出了SQL查询语句，适用于SQLite和MySQL数据库。

SQL练习68：牛客每个人最近的登录日期3

题目链接：牛客网

题目描述
牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率，
有一个登录(login)记录表，简况如下:
在这里插入图片描述
第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了牛客网
。。。
第4行表示id为3的用户在2020-10-12使用了客户端id为2的设备登录了牛客网
。。。
最后1行表示id为1的用户在2020-10-14使用了客户端id为2的设备登录了牛客网

请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)，上面的例子查询结果如下:
在这里插入图片描述
查询结果表明:
id为1的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存
id为2的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存
id为3的用户在2020-10-12第一次新登录了，在2020-10-13没登录了，算是失败的留存
id为4的用户在2020-10-13第一次新登录了，在2020-10-14没登录了，算是失败的留存
固次日成功的留存率为 2/4=0.5
(sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, ‘+1 day’)，四舍五入的函数为round，sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5
mysql里查找某一天的后一天的用法是:DATE_ADD(yyyy-mm-dd,INTERVAL 1 DAY)，四舍五入的函数为round)

解法
根据题目的要求可以先通过子查询，获取各个用户登录之后一天的日期，再在login表中查询user_id, date在子查询结果中的数据，使用COUNT(DISTINCT user_id)*1.0统计留存到的人数，SELECT COUNT(DISTINCT user_id) FROM login获取总用户人数，round()函数保留三位小数。

SELECT round(COUNT(DISTINCT user_id)*1.0/(SELECT COUNT(DISTINCT user_id) FROM login) ,3)
FROM login
WHERE (user_id, date) IN (SELECT user_id, DATE_ADD(MIN(date), INTERVAL 1 DAY)
                          FROM login 
                          GROUP BY user_id);