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 rn
    from 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

 本文SQL题目来源于牛客网

 

 

 

 

 

 

 

 

 

 

 

posted @ 2021-06-10 00:11  Transkai  阅读(574)  评论(0编辑  收藏  举报