SQL实战 9.高频SQL面试题网站访客最近登录日期系列

最近登录日期(一)

描述

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

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

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了该网站
。。。
第4行表示user_id为3的用户在2020-10-13使用了客户端id为2的设备登录了该网站
还有一个用户(user)表，简况如下:

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

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

fh最近的登录日期在2020-10-13，而且是使用ios登录的
wangchao最近的登录日期也是2020-10-13，而且是使用ios登录的

SQL如下

select  u_n,c_n ,date from 
(
select a.user_id,a.date,b.name as u_n,c.name as c_n,row_number() over(partition by user_id order by date desc)as rnfrom login a left join user b on a.user_id=b.id left join client c on a.client_id=c.id
)a where rn=1 
order by u_n

最近登录日期(二)

描述

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

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了该网站
。。。

第4行表示user_id为3的用户在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)

SQL如下

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);

最近登录日期(三)

描述

牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数，
有一个登录(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，没有新用户登录

SQL如下

解法一：暴力破解法，求出每个用户的首次登陆日期，并统计每个日期的人数即可，但题目要求当天没有人的话要给出0，所以这里需要join一下自己，取没有用户登陆的日期；

select a.date,case when b.cnt is null then 0 else b.cnt end as cnt from 
(select distinct date from login )a left join 
(select date,count(1) as cnt from 
(
select id,user_id,client_id,date,
row_number() over(partition by user_id order by date) as rn from login a 
)a 
where rn=1 group by date
) b
on a.date=b.date

解法二：用 (user_id,date) in (user_id,min(date)) 得到每天登陆的新用户，最后用sum case when来统计

select distinct date,
sum(case when (user_id,date) in (select user_id,min(date) from login group by user_id )then 1 else 0 end   )
from login group by date order by 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语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:

查询结果表明:
2020-10-12登录了3个(user_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个(user_id为4)新用户，2020-10-15，user_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)

SQL如下

select c.date,
round(count(d.user_id)/count(*),3)as p
from
(select a.date,b.user_id from
(select distinct l1.date from login l1)a left join 
(select l2.user_id,min(l2.date) as f_date from login l2 group by l2.user_id)b
on a.date=b.f_date
) as c left join (select distinct l3.user_id
from login l3,login l4
where l3.user_id=l4.user_id
and DATE_ADD(l3.date,INTERVAL 1 DAY)=l4.date) as d on c.user_id = d.user_id
group by c.date

最近登录日期(五)

描述

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

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
。。。
第5行表示user_id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网
有一个刷题(passing_number)表，简况如下:

第1行表示user_id为2的用户在2020-10-12通过了4个题目。
。。。
第3行表示user_id为1的用户在2020-10-13提交了代码但是没有通过任何题目。
第4行表示user_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

解题思路

1.先找出每个用户都在那一天登陆了：

select distinct user_id,date from login

2.然后去与passing_number表关联小于或等于当前日期的数据，当然也别忘了关联user表去取一下name：

select c.name,a.user_id ,a.date,b.number from 
(select distinct user_id,date from login) a,
passing_number b ,user c    where a.user_id=b.user_id and a.user_id =c.id 
and b.date<=a.date

3.最后求每个user每天的刷题数量，用sum()+group by 即可：

SQL如下

select name,date,sum(number) from 
(
select c.name,a.user_id ,a.date,b.number from 
(select distinct user_id,date from login) a,
passing_number b ,user c    where a.user_id=b.user_id and a.user_id =c.id 
and b.date<=a.date
) b group by name ,date order by date, name