2019年秋招部分大厂SQL题

数据管道

共 3994字,需浏览 8分钟

 ·

2020-07-28 15:47

突发奇想,翻出去年秋招时候的几道笔试题做一做,看看我这刀是不是老了。总的来说我遇到的题目难度都还OK,不是很难,耐心、细心一点是没有什么问题的。
不嫌弃的话就一起来练练手吧。


01

某滴有一张订单表ordr_list,共有三列:ordr_id(订单id)-- bigint,uid(乘客id)-- bigint,start_time(发单时间)-- datetime,对于同一个乘客,每个订单的发单时间均不同。

解题要求:

1)写SQL求得每个用户最早发单的订单id,每个乘客一行;

2)输出两列:uid(乘客id),ordr_id(最早发单的订单id);

3)可使用union、left join、case when等,不能使用右连接、全连接、row_number()、rank()

select a.uid, a.ordr_idfrom ordr_list as aleft join (  select uid, min(start_time) as min_time    from ordr_list    group by uid) as b on a.uid = b.uid and a.start_time = b.min_time;
注意:

1.做题时遵守题目要求;

2.注意题中给的数据库系统的类型,此题给的是SQLite3,有些函数不能用或不一致;

3.此题明明写了同一乘客,每个订单的发单时间均不同,但笔试的时候直接按上述代码做,并未运行通过,需要如下解法:
select uid, min(ordr_id)from(  select a.uid, a.ordr_id   from ordr_list as a   left join    (  select uid, min(start_time) as min_time      from ordr_list      group by uid   ) as b    on a.uid = b.uid and a.start_time =b.min_time)tgroup by uid;
02
某巴巴有一张订单表table1,表中包含user_id(用户id),dt(购买日期),amt(购买金额),找出购买天数和购买金额最多的用户,按要求输出用户id、购买天数、购买金额、备注是购买天数最多还是购买金额最多。

解法1:

with tmp as (    select user_id, count(distinct dt) as sum_dt, sum(amt) as sum_amt    from table1    group by user_id)
select user_id, sum_dt, sum_amt, '购买天数最多' as infofrom tmp where sum_dt = (select max(sum_dt) from tmp)union all select user_id, sum_dt, sum_amt, '购买金额最多' as infofrom tmp where sum_amt = (select max(sum_amt) from tmp);

解法2:

select TOP 1 user_id, count(distinct dt), sum(amt), '购买天数最多' as infofrom table1 group by user_idorder by count(distinct dt) descunion allselect TOP 1 user_id, count(), sum(amt), '购买金额最多' as infofrom table1 group by user_idorder by sum(amt) desc;
但需要注意的是,并非所有的数据库系统都支持此解法中的TOP。
03

某浪有一用户观看视频的行为记录表user_video,一次观看即一条记录,表结构大致如下,其中date,uid,vid为主键:

date(日期)
uid(用户id)
vid(视频id)
tag(视频标签)
20180711
1001
2001
体育
20180711
10022002娱乐
...
...
...
...

每天新增用户的信息存于新增用户表中,表名为new_user_info,表结构大致如下,其中uid为主键:

uid(用户id)
new_date(日期)
new_chl(渠道)
new_chl_type(渠道类型)
1013
2018-06-30
11010
应用宝
1015
2018-07-0112010应用商店
...
...
...
...

用户信息存于用户信息表中,表名为user_info,表结构大致如下,其中uid为主键:

uid(用户id)gender(性别)
age(年龄)
city(城市)
1001

20
北京
100225上海
...
...
...
...

1) 抽取各渠道类型的新增用户在7月10日,7月11日的人均观看次数,统计7月11日人均观看次数对比7月10日的涨幅,输出结果:第一列为渠道类型,第二列为人均观看次数,第三列为涨幅(保留4位小数,四舍五入)。

select new_chl_type      ,round(sum(vid)/count(distinct uid), 4)      ,round((sum(case when date = '20200711' then vid else 0 end)/count(distinct case when date = '20200711' then uid else null end))      /(sum(case when date = '20200710' then vid else 0 end)/count(distinct case when date = '20200710' then uid else null end)- 1, 4)from (    select a.uid, a.new_chl_type, b.date, b.vid    from new_user_info as a     left join     (        select date, uid, count(vid) as vid         from user_video         where date <= '20190711' and date >= '20190710'        group by date, uid
) as b on a.uid = b.uid ) as tgroup by new_chl_type;

2) 取25岁以下女性用户在美食标签2019年8月1日-7日观看视频次数超过10次且8月8日观看次数小于3次的用户,输出结果:输出符合条件的用户id。

select a.uidfrom (    select uid     from user_info     where age < 25 and gender = '女'    group by uid) as a left join (    select uid, count(vid)    from user_video    where tag = '美食' and date <= '20190807' and date >= '20190801'    group by uid     having count(vid) > 10) as b on a.uid = b.uid left join (    select uid     from user_video    where tag = '美食' and date = '20190808'    group by uid     having count(uid) < 3) as c on a.uid = c.uid where b.uid is not null and c.uid is not null;
注意:
多表连接可能导致效率变低,此处也可以考虑使用case when进行条件判断,然后再筛选。

04

某多有一张用户订单表,表名为ordr_tbl,字段为user_id(用户id),ordr_id(订单id),ordr_goods(订单商品),ordr_time(下单时间)。还有一张用户商品点击明细表,表名为clk_tbl,字段为clk_id(点击id),user_id(用户id),clk_time(点击时间),clk_goods(点击商品名)。假设只有一天数据,点击和下单必须是同一用户同一商品,多次点击然后下单的话算最后一次点击。输出用户点击后下单的记录:clk_id,ordr_id。
select a.clk_id, b.ordr_idfrom (    select *    from     (        select *, row_number() over(partition by user_id, clk_goods order by clk_time desc) as rnk         from clk_tbl    ) as t    where rnk = 1) as a left join ordr_tbl as b on a.user_id = b.user_id and a.clk_goods = b.ordr_goods and a.clk_time < b.ordr_time;

注意:

不要丢失重要的限定条件,比如对于成交来说,点击时间肯定是早于下单时间的。

05

附赠一道求留存的题目,模糊记忆,是提前批的时候英语流利说考到的,那时候那道题可是难倒了周围不少小伙伴。不是题目本身难,而是因为不熟悉留存的定义,无法向下进行,而这道题目对于实习过的同学来说可能就比较友好了。

有一张活跃用户表active_user,包括两个字段:pt('年-月-日'),user_id(用户id),求当天用户在第二天、第三天的回访比例。

select a.pt       ,count(distinct case when a.pt = date_sub(b.pt, 1) then a.user_id else null end)/count(distinct a.user_id) as '第二天回访比例'      ,count(distinct case when a.pt = date_sub(b.pt, 2) then a.user_id else null end)/count(distinct a.user_id) as '第三天回访比例'from active_user as aleft join active_user as bon a.user_id = b.user_idgroup by a.pt;

注意:

第二天的回访比例也就是常说的次留(次日留存率),上述方法就是以user_id为连接键,通过判断当天活跃的用户在第二天(第n天)还在不在来计数求取。除求留存外,求新增用户也常出现在笔面试中。


最后对先前推文中第一题的答案给出纠正。

原文链接在这:解一下TMD几道热门数据分析面试题。

select user_name, goods_kindfrom(    select user_name, goods_kind, num, row_number() over(partition by user_name order by num desc) as rnk     from    (        select user_name, goods_kind, count(1) as num         from user_goods_table        group by user_name, goods_kind    ) as t1) as  t2 where rnk = 1;
其实原答案思路与这个是一致的,只是有些函数及组合不能并列使用,或者说并列使用并不符合逻辑。在使用SQL做题的时候,如果无法从原表中直接得到想要的数据,就需要先进行一次select,形成一个子查询。而此子查询能否成立需要在脑中形成一个表,这个表的各个列及行数据是否能准确形成,是否符合逻辑,以此进行判断。原答案中goods_kind如何与rank进行对应就是个问题了,可以考虑看看。

写在最后:

1.对于SQL题目,其实正经来说,比算法coding要简单的多。最基本的要求就是知道基础的函数及使用方法,就像做数学题一样,遇到什么类型的题目,需要用到什么样的解法,做的多了,自然也就熟悉了,也就会更熟练。

2.做题时注意题目中所给的条件,一些条件是显性的,如“超过”、“小于”、“最早”、“最多”、“几分之几”、“前百分之多少”等等;一些条件是隐性的,需要自己细心才能注意到,如上题中“点击时间小于下单时间”,还有一些题目中“快递邮寄时间早于快递接收时间”等。

3.有自己良好的书写习惯和风格,是先写大框架再填补,还是从内而外书写SQL语句,是通过空格或tab进行缩进来标志不同的子查询还是直接通过括号就能识别出来。好的习惯不仅给别人的可读性好,给自己在遇到逻辑毕竟复杂的题目时,也留有了后路。

4.做完题目记得复查。子查询是否定义了别名、表连接时select出两表中同名字段时需要加表别名、各个条件是否已与题目中条件一一对应等等,犯错的次数多了,记忆会更加深刻,也会更加注意,要学会总结。

5.最后再强调一次,想刷SQL题目,除了牛客和leetcode,请从牛客上找笔面经中的SQL题目练习,从各种公众号推文中练习。以后再有人问我从哪里练习SQL题,此种问题一概不回答。

6.上述题目有错误的地方或更好的方法欢迎指正。

浏览 19
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报