sql面试题1

如题所述

第1个回答  2022-07-20
【1】腾讯面试题

table_A ( 用户userid和登录时间time)求连续登录3天的用户数

https://www.cnblogs.com/ikww/p/12012831.html

【SQL】查询连续登陆7天以上的用户

查询7天连续登陆用户这个问题很经典,解决方法也有很多,这里我讲一下笔者的方法,希望对大家有帮助。

具体思路:

1、因为每天用户登录次数可能不止一次,所以需要先将用户每天的登录日期去重。

2、再用row_number() over(partition by _ order by _)函数将用户id分组,按照登陆时间进行排序。

3、计算登录日期减去第二步骤得到的结果值,用户连续登陆情况下,每次相减的结果都相同。

4、按照id和日期分组并求和,筛选大于等于7的即为连续7天登陆的用户。

表信息如下图

第一步:用户登录日期去重

1select DISTINCT date(date) as 日期,id from orde;

 结果为:

第二步:用row_number() over()函数计数

1select *,row_number() over(PARTITION by id order by 日期) as cum from (select DISTINCT date(date) as 日期,id from orde)a;

结果为:

第三步:日期减去计数值得到结果

1select *,date(日期)-cum as 结果 from (select *,row_number() over(PARTITION by id order by 日期) as cum from (select DISTINCT date(date) as 日期,id from orde)a)b;

结果:

第四步:根据id和结果分组并计算总和,大于等于7的即为连续登陆7天的用户

1select id,count(*) from (select *,date(日期)-cum as 结果 from (select *,row_number() over(PARTITION by id order by 日期) as cum from (select DISTINCT date(date) as 日期,id from orde)a)b)c GROUP BY id,结果 having count(*)>=7;

结果为:

用了多次嵌套查询,最终得到我们需要的结果。

01

01.还原题目场景 - 建表

select*fromtmp.tmp_last_3_day;

02

02.解决问题关键 - 分析

03

03.见证奇迹的时刻 - 实现

  连续3天登录的用户id ­­ sql如下:

我们再来看下结果:

04

04.面试反思总结 - 升华

现在看来,其实也不难嘛!思路很重要,有了思路,问题自然迎刃而解(此处有掌声)。。ps:六师妹说今晚的加鸡腿~哈哈

05

05.条条大路通罗马

其实,方法有很多,上面只是选了一种技巧性比较强的一种。下面也是一种不错的方法,大佬们肯定还有其他实现方式,欢迎小伙伴们右下角点[在看]评论留言,一起讨论,一起进步 ~ go go go ...

【2】滴滴面试题

学生表:tb_student(name:学生姓名,id:学号,class:班级,in_time:入学时间,age:年龄,sex:性别,major:专业)

学生成绩表:tb_score(id:学号,course:课程,score:分数)

(1)筛选出2017年入学的“计算机”专业年龄最小的10位同学名单(姓名、学号、班级、年龄)

(2)统计每个班同学各科成绩平均分大于80分的人数和人数占比

select a.class,

count(case when a.avg_score>80 then 1 else null end) as '人数'

count(case when a.avg_score>80 then 1 else null end)/count(a.id) as '人数占比'

from

(select st.class,sc.id,avg(sc.score) as avg_score

from tb_student st

inner join tb_score sc

on st.id=sc.id

group by 1)a

(1)select st.name,st.id,st.class,st.age

from tb_student st

inner join tb_score sc

on st.id=sc.id

where year(st.in_time)='2017'

and major='计算机'

order by age asc

limit 0,9

(2)

链接:https://zhuanlan.zhihu.com/p/110924684

(1)

selectname,id,class,agefromtb_studentwhereyear(in_time)=2017andmajor='计算机'ORDERBYageASClimit10;

(2)

SELECTa.class,sum(casewhenaaa.x>80then1else0end)asnum_80,(sum(casewhenaaa.x>80then1else0end)/count(*))asproportionfromtb_studentaINNERJOIN(SELECTid,avg(score)asxfromtb_scoreGROUPBYid)asaaaona.id=aaa.idGROUPBYclass;

解析:写一个子查询,从score表中得到以学号分组的学生各科平均分。命名为aaa,将其与学生表内联结。再以class分组,得到以class分组的各班学生的平均分,最后通过case语句,sum聚合函数得到平均分>80分的计数,和所占各班总人数比例。

case语句也可以用if语句来代替

写法2:

SELECTa.class,count(if(aaa.avg>80,true,null))asnumover80,count(if(aaa.avg>80,true,null))/count(a.id)astotalfromtb_studentaINNERJOIN(SELECTid,avg(score)asxfromtb_scoreGROUPBYid)asaaaona.id=aaa.idGROUPBYclass;
相似回答
大家正在搜