SQL实战 9.高频SQL面试题 网站访客最近登录日期系列
最近登录日期(一)
描述
某网站每天有很多人登录,请你统计一下该网站每个用户最近登录是哪一天,用的是什么设备.
第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了该网站
。。。
第4行表示user_id为3的用户在2020-10-13使用了客户端id为2的设备登录了该网站
还有一个用户(user)表,简况如下:
![](https://uploadfiles.nowcoder.com/images/20200817/557336_1597652611050_C098FF7CF52D3DC2ECE5019F4E7A5E88)
还有一个客户端(client)表,简况如下:
![](https://uploadfiles.nowcoder.com/images/20200817/557336_1597652618264_F2C14AA3F53E74C2FE5A266283E56241)
请你写出一个sql语句查询每个用户最近一天登录的日子,用户的名字,以及用户用的设备的名字,并且查询结果按照user的name升序排序,上面的例子查询结果如下:
查询结果表明:
fh最近的登录日期在2020-10-13,而且是使用ios登录的
wangchao最近的登录日期也是2020-10-13,而且是使用ios登录的
有一个登录(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)记录表,简况如下:
![](https://uploadfiles.nowcoder.com/images/20201026/557336_1603701796116_54BBC7EED94F0CAAFEC8DB042BBBCD01)
第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了该网站
。。。
请你写出一个sql语句查询新登录用户次日成功的留存率,即第1天登陆之后,第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入),上面的例子查询结果如下:
![](https://uploadfiles.nowcoder.com/images/20200820/557336_1597903513290_CD24AA22EB7078641AEAF29B146D26FD)
有一个登录(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)记录表,简况如下:
![](https://uploadfiles.nowcoder.com/images/20200820/557336_1597903671918_2C3C4BF94A59FB9AEEA6FEA89DEE17C9)
第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语句查询每个日期登录新用户个数,并且查询结果按照日期升序排序,上面的例子查询结果如下:
![](https://uploadfiles.nowcoder.com/images/20200820/557336_1597903683115_47DE8F52D6F847E788BB9EF6279481C4)
查询结果表明:
2020-10-12,有3个新用户(user_id为2,3,1)登录
2020-10-13,没有新用户登录
2020-10-14,有1个新用户(user_id为4)登录
2020-10-15,没有新用户登录
有一个登录(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)记录表,简况如下:
![](https://uploadfiles.nowcoder.com/images/20200820/557336_1597903752757_A02F3DF1419BC2D3D4EE9B2B4557053B)
第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位之后的四舍五入),并且查询结果按照日期升序排序,上面的例子查询结果如下:
![](https://uploadfiles.nowcoder.com/images/20200820/557336_1597903761838_F734DB69B9941F0DF86776922B0CF347)
查询结果表明:
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;
有一个登录(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)记录表,简况如下:
![](https://uploadfiles.nowcoder.com/images/20200820/557336_1597903934415_A5E822A1E15CEBC7E16183ECD9D7CC7A)
第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
。。。
第5行表示user_id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网
有一个刷题(passing_number)表,简况如下:
![](https://uploadfiles.nowcoder.com/images/20200820/557336_1597903944909_9C60ECB78BE56145269BDFA74F1074D3)
第1行表示user_id为2的用户在2020-10-12通过了4个题目。
。。。
第3行表示user_id为1的用户在2020-10-13提交了代码但是没有通过任何题目。
第4行表示user_id为4的用户在2020-10-13通过了2个题目
还有一个用户(user)表,简况如下:
![](https://uploadfiles.nowcoder.com/images/20200820/557336_1597903952301_4763AF7B377AF42CB5A87C53B965DC45)
查询结果表明:
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
有一个登录(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题目来源于牛客网