突发奇想，翻出去年秋招时候的几道笔试题做一做，看看我这刀是不是老了。总的来说我遇到的题目难度都还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.上述题目有错误的地方或更好的方法欢迎指正。
扫码关注我吧
转载：https://blog.csdn.net/lee__leee/article/details/107603119