SQL做题日志9||牛客每个人最近的登录日期

Inochigohan
474 阅读6分钟

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

🔖文章摘要

题目选自牛客网在线编程SQL实战，文章包含一组题，题目难度依次加深，内容依次为：①题目介绍、②代码编写要求、③解题思路、④解题代码和⑤要点总结共五大部分。

📝题目一（难度：简单）

📌题目介绍

牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天。

有一个登录(login)记录表，简况如下:

内容说明：

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
... ...
第4行表示user_id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网\

📌编写要求

写出一个sql语句查询每个用户最近一天登录的日子，并且按照user_id升序排序，上面的例子查询结果如下:

查询结果表明:
user_id为2的最近的登录日期在2020-10-13
user_id为3的最近的登录日期也是2020-10-13

📌解题思路

📌解题代码

select user_id,max(date) as d
from login
group by user_id
order by user_id;

📌要点总结

  • 主要考察分组group by、排序order by和max函数的使用，比较简单

📝题目二（难度：较难）

📌题目介绍

牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天，用的是什么设备.

有一个登录(login)记录表，简况如下:

内容说明：

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
... ...
第4行表示user_id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网

还有一个用户(user)表和客户端(client)表，简况如下:

image.png

📌编写要求：

写出一个sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序，上面的例子查询结果如下:

查询结果表明:
fh最近的登录日期在2020-10-13，而且是使用ios登录的
wangchao最近的登录日期也是2020-10-13，而且是使用ios登录的

📌解题思路

📌解题代码

select user.name as u_n,client.name as c_n,ctable.md as date
from (
    select user_id,max(date) as md
    from login
    group by user_id
) as ctable
left join user on ctable.user_id=user.id
left join login on user.id=login.user_id
left join client on client.id=login.client_id
group by ctable.user_id
order by user.name;

📌要点总结

  • 可使用where+子查询简化代码

    select user.name as u_n,client.name as c_n, login.date
from login 
left join user on login.user_id=user.id
left join client on login.client_id=client.id
where (user_id, date) in (
    select user_id,max(date) as md from login
    group by user_id
) order by user.name;

  •  使用多表连接时需注意区分主从表以及连接的条件

📝题目三（难度：较难）

📌题目介绍

牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率，有一个登录(login)记录表，简况如下:

内容说明：

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了牛客网
... ...
第4行表示user_id为2的用户在2020-10-12使用了客户端id为2的设备登录了牛客网
... ...
最后1行表示user_id为1的用户在2020-10-14使用了客户端id为2的设备登录了牛客网

📌编写要求

请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)，上面的例子查询结果如下:

查询结果表明:

user_id为1的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存
user_id为2的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存
user_id为3的用户在2020-10-12第一次新登录了，在2020-10-13没登录了，算是失败的留存
user_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)

📌解题思路

📌解题代码

SELECT round(count(user_id)/ (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 );

📌要点总结

  • 需要理解留存率的计算公式为：（第一天和第二天都登录的用户数）/（总用户数）
  • 考查where+子查询的使用，(user_id, date)查询的是user_id和date二者都满足条件的数据

📝题目四（难度：较难）

📌题目介绍

牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数，有一个登录(login)记录表，简况如下:

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网，因为是第1次登录，所以是新用户
... ...
第4行表示user_id为2的用户在2020-10-13使用了客户端id为2的设备登录了牛客网，因为是第2次登录，所以是老用户
... ...
最后1行表示user_id为4的用户在2020-10-15使用了客户端id为1的设备登录了牛客网，因为是第2次登录，所以是老用户

📌编写要求：

写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，上面的例子查询结果如下:

查询结果表明:
2020-10-12，有3个新用户(user_id为2，3，1)登录
2020-10-13，没有新用户登录
2020-10-14，有1个新用户(user_id为4)登录
2020-10-15，没有新用户登录

📌解题思路

📌解题代码：

with table2 as (
    select distinct new_date, count(*)over(partition by new_date) as new
    from ( select user_id, min(date) as new_date from login
    group by user_id ) as table1 
) select distinct date, ifnull(new,0)
from login left join table2
on login.date=table2.new_date;

📌要点总结

  • 需要理清每一步骤的输出结果，熟悉ifnull函数的用法

  • 窗口+case函数的解法：①使用row_number函数对每个用户各自的登录日期进行排序，序号为1的日期就是每个用户初始登录的日期   ②使用case将序号为1 的日期设为1不然为0，并使用sum函数进行统计，并按日期进行分组，得出想要的查询结果

    select table1.date,
sum(case when t_rank=1 then 1 else 0 end) as new
from 
(select date, row_number() over(partition by user_id order by date) as t_rank
from login) as table1
group by date;
avatar
文章
阅读
粉丝