2019年秋招部分大厂SQL题

突发奇想，翻出去年秋招时候的几道笔试题做一做，看看我这刀是不是老了。总的来说我遇到的题目难度都还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_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;

注意：

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
)t
group 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 info
from tmp 
where sum_dt = (select max(sum_dt) from tmp)
union all 
select user_id, sum_dt, sum_amt, '购买金额最多' as info
from tmp 
where sum_amt = (select max(sum_amt) from tmp);

解法2：

select TOP 1 user_id, count(distinct dt), sum(amt), '购买天数最多' as info
from table1 
group by user_id
order by count(distinct dt) desc
union all
select TOP 1 user_id, count(), sum(amt), '购买金额最多' as info
from table1 
group by user_id
order by sum(amt) desc;

但需要注意的是，并非所有的数据库系统都支持此解法中的TOP。

03

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

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

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

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

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

uid(用户id)	gender(性别)	age(年龄)	city(城市)
1001	女	20	北京
1002	男	25	上海
...	...	...	...

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 t
group by new_chl_type;

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

select a.uid
from 
(
    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_id
from 
(
    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 a
left join active_user as b
on a.user_id = b.user_id
group by a.pt;

注意：

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

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

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

select user_name, goods_kind
from
(
    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.上述题目有错误的地方或更好的方法欢迎指正。

扫码关注我吧