SQL练习13-牛客网66-71题-最近登陆日期

链接：www.nowcoder.com/ta/sql?page…

$\color{red}{第68题}$ 次日留存
$\color{red}{第69题}$ 每日登录新用户个数
$\color{red}{第70题}$ 次日留存率(没有新客登录的日期如何处理)
$\color{red}{第71题}$ 截止到某天，累计通过的题目数量

66. 牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天。 有一个登录(login)记录表，简况如下:
    

第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
...
第4行表示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)
from login
group by user_id
order by user_id

67. 除了上题中的login表，还有一个用户(user)表，简况如下:

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

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

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

select u.name,c.name, max(date)
from login l
join user u on l.user_id=u.id
join client c on l.client_id=c.id
group by l.user_id
order by u.name

68. 牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率， 有一个登录(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(min(date),INTERVAL 1 DAY)，四舍五入的函数为round)

select round(count(b.date)*1.0/count(a.user_id),3)
from
(select user_id, min(date) d
from login 
group by user_id) a
left join login b on a.user_id=b.user_id and date(d, '+1 day')=b.date

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

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

错误：

select l2.date, ifnull(count(*),0)
from login l2
join
(select user_id, min(date) m_date
from login l1
group by user_id) a
on l2.user_id=a.user_id and l2.date=a.m_date
group by l2.date

错误原因：分组计数后为空值的未显示

修改：

select l2.date, ifnull(count,0)
from login l2
left join
(select a.m_date,count(user_id) count from
(select user_id, min(date) m_date
from login l1
group by user_id) a
group by a.m_date) b
on l2.date=b.m_date
group by l2.date

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

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

请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:

查询结果表明:
2020-10-12登录了3个(id为2，3，1)新用户，2020-10-13，只有2个(id为2,1)登录，故2020-10-12新用户次日留存率为2/3=0.667;
2020-10-13没有新用户登录，输出0.000;
2020-10-14登录了1个(id为4)新用户，2020-10-15，id为4的用户登录，故2020-10-14新用户次日留存率为1/1=1.000;
2020-10-15没有新用户登录，输出0.000;
(注意:sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, '+1 day')，sqlite里1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5)

错误：

select b.m_date, round(ifnull(count(*)*1.0/count(b.user_id),0),3)
from
(select a.m_date, a.user_id from
(select user_id,min(date) m_date
from login
group by user_id) a
group by a.m_date) b
join login l on b.user_id=l.user_id 
where date(b.m_date,'+1 day')=l.date
group by b.m_date
order by b.m_date

次日留存为0的未显示

修改：

select a.m_date, round(count(l.user_id)*1.0/count(a.user_id),3)
from(
    select user_id,min(date) m_date
    from login
    group by user_id) a
left join login l on a.user_id=l.user_id 
and date(a.m_date,'+1 day')=l.date
group by a.m_date
union
select date,0.000 as p
from login
where date not in (
    select min(date)
    from login
    group by user_id)
order by date

71. 牛客每天有很多人登录，请你统计一下牛客每个用户查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。 不存在没有登录却刷题的情况，但是存在登录了没刷题的情况，不会存在刷题表里面，有提交代码没有通过的情况，但是会记录在刷题表里，只不过通过数目是0。
    有一个登录(login)记录表，简况如下:

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

有一个刷题（passing_number)表，简况如下:

第1行表示id为2的用户在2020-10-12通过了4个题目。
。。。
第3行表示id为1的用户在2020-10-13提交了代码但是没有通过任何题目。
第4行表示id为4的用户在2020-10-13通过了2个题目

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

请你写出一个sql语句查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题，并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出，上面的例子查询结果如下:

查询结果表明: fh在2020-10-12为止，总共通过了4道题，输出为4
wangchao在2020-10-12为止，总共通过了1道题，总计为1
tm在2020-10-12为止只登陆了没有刷题，故没有显示出来
tm在2020-10-13为止刷了题，但是却没有通过任何题目，总计为0
wangchao在2020-10-13通过2道，但是加上前面2020-10-12通过1道，故在2020-10-13为止总共通过了3道题，总计为3

select name,date,
    (select sum(p.number) 
     from passing_number p 
     where p.user_id=a.user_id and p.date<= a.date 
     group by p.user_id) as p
from (
    select user_id, name,date,number 
    from passing_number 
    join user on user.id=passing_number.user_id) a
order by date ,name

最简单的解法：

select name as u_n,date,sum(number) over(partition by user_id order by date) as ps_num
from passing_number p
left join user u
on p.user_id=u.id
order by date,u_n

不需要使用到第一个login表，采用sum函数进行开窗处理，将user_id进行分区，再通过时间升序排序，进而实现了在每个user_id分区中以升序日期排序的通过题数的逐个递加（在这一步中，user_id的排序不重要，date的排序才重要），即“截止到某天，累计总共通过了多少题”的题意。