【数据分析面试】大厂高频SQL笔试题(四)

写在前面

数据分析SQL笔试题系列第4篇来啦!之前笔试题的文章如果没有看可以戳:

无论你是刚毕业的职场小鲜肉、还是想转行数据分析的小白玩家,只要想进入数据分析的行业,都逃不过数据分析面试的考验,这里最重要也是最关键的一关就是SQL笔试了,不过不用担心,结合作者6年+的工作和面试经验,系统全面地整理了数据分析面试中那些高频出现的以及各大厂的SQL笔试题,学习了这些笔试题的常见套路和解法,把这些题目都刷一遍,在接下来的笔试中应该可以一往无前,收割offer啦!

SQL笔试不仅考查代码的熟练程度,更多的考查的是对业务的理解程度,只有理解了业务背景和逻辑,才能更快速准确地给出答案。本篇选取了PDD的业务笔试题,在某个业务背景下考查SQL能力,赶紧来看看这些题目你都会么?
【数据分析面试】大厂高频SQL笔试题(四)

用户行为分析

业务背景
某购物APP最近上线了一个新功能,用户签到后可以跳转到大转盘抽奖,抽奖获得的奖金可以抵消购物的费用,以此来培养用户使用app的习惯。
数据表介绍
现有一张用户行为表user_log,主要字段如下,记录了用户在app上的所有行为日志,即何人user_id在何时event_time进行了什么操作event_id。
【数据分析面试】大厂高频SQL笔试题(四)

需求:
1、计算该app每天的访客数以及每天人均行为次数。
2、统计每天签到之后并进行抽奖的用户数,注意签到和抽奖行为必须相邻(签到和抽奖行为对应的event_id分别为’register’,‘gift’)。

思路:
第1问比较简单,计算app每天的访客数,因为用户登录访问app就会在表中产生对应的行为日志,所以每天的访客数只需要按天对用户数去重即可,每天人均行为次数的计算,因为一次行为就会产生一条记录,所以,人均行为次数就是所有的记录计数,除以总的访客数。
代码如下:
select cast(event_time as date) as day,
count(distinct user_id) as active_cnt,
count( event_id)/count(distinct user_id) as avg_opr_cnt
from user_log
group by cast(event_time as date);
第2问升级了难度,虽然也是统计用户数,但是添加了限制:签到之后要大转盘抽奖,两个行为一前一后必须相邻才可以。这个时候我们可以用窗口函数的位移函数lead()over()实现,lead可以取当前记录的下一条记录,如果我们对每个用户user_id分组,按照行为时间event_time升序排列,就可以得到一个用户的连续的行为记录,再用lead()就可以得到下一条记录,从而在当前记录中得到下一条记录,对两个连续行为进行筛选,就可以计算满足这个条件的用户数。
代码如下:
select a.day,count(distinct user_id)
from(
select user_id,
cast(event_time as date) as day,
event_id,
lead(event_id,1) over(partition by user_id order by event_time ) as next_event_id
from user_log
)a
where event_id=‘register’ and next_event_id=‘gift’
group by a.day;

活动效果数据分析

业务背景
为了提高某店铺的营业额,最近运营部门上线了多个运营活动,用户参与活动后可以领取不定额的优惠券,以此鼓励用户下单。但每个用户限参与一个活动,不可同时参与多个活动。
数据表介绍
现有一张订单表orders和活动报名表act_join,分别记录了用户的下单明细和用户报名的活动明细。具体字段如下:
订单表orders,大概字段有(user_id‘用户编号’,order_id ‘订单编号’ ,order_sales‘订单金额’ , order_time‘下单时间’)。
【数据分析面试】大厂高频SQL笔试题(四)
活动报名表act_join,大概字段有(act_id‘活动编号’, user_id‘报名用户’,join_time‘报名时间’)
【数据分析面试】大厂高频SQL笔试题(四)

需求:

  1. 统计每个活动报名的所有用户在报名后产生的总订单金额,总订单数。(每个用户限报一个活动,且默认用户报名后产生的订单均为参加活动的订单)。
  2. 统计每个活动从开始后到当天(数据统计日)平均每天产生的订单数,活动开始时间定义为最早有用户报名的时间。

思路:
第1问计算总订单金额和总订单数,这两个指标都比较简单sum(order_sales)、count(order_id)就可以,但是关键在于限定条件,是每个活动报名后的用户的汇总,必须是报名了某个活动,且必须在活动开始后的数据统计。可以通过订单表orders和报名表act_join连接,限定订单时间大于等于活动的开始时间即可。
代码如下:
select t2.act_id,
count(t1.order_time) as order_cnt,
sum(order_sales) as order_sales_sum
from
(
select user_id,order_id,order_sales,order_time
from orders
)t1
inner join (
select user_id,act_id,join_time
from act_join
)t2
on t1.user_id=t2.user_id
where t1.order_time>=t2.join_time
group by t2.act_id;
第2问与第1问有相似之处,同样是用户报名后的下单,只是多了一些限定条件:同时要满足要小于等于计算日期当天,也就是程序运行的系统时间now(),在此基础上,计算整体的订单数,除以活动进行的天数,就是该活动每天的平均下单数。
代码如下:
select t1.act_id,
count(order_id)/datediff(now(),min(t1.begin_time))–总店单数/活动天数
from
(
select act_id,
user_id,
join_time,
min(join_time) over(partition by act_id) as begin_time --当前活动的开始时间
from act_join
)t1
inner join
(
select user_id,
order_id,
order_time
from orders
)t2
on t1.user_id=t2.user_id
where t1.join_time between t1.begin_time and now() --活动开始至今的数据
and t2.order_time >= t1.join_time --活动开始后的下单
group by t1.act_id;
说明:这里使用了窗口函数,计算了每个活动的开始时间,然后join 订单表,通过where条件将上面的限定条件满足。当然这只是一种解题思路,还有很多其他的解法,大家可以尝试。
上面通过2个实际的业务场景,不仅考查了SQL的代码能力,更主要的是考查了大家对业务场景的理解能力,如果理解不了业务场景,也就很难写出SQL代码,相反,如果业务场景非常熟悉,代码就比较简单了,所以,我们在练习SQL的时候,一定要结合着业务场景来练,这样才比较贴合实际业务场景,也更能在笔试中有余,脱颖而出啦!
以上就是数分面试宝典系列—SQL高频笔试题第4篇文章的内容,部分历史文章请回翻公众号,更多数据分析面试笔试的文章持续更新中,敬请期待,如果觉得不错,也欢迎分享、点赞和收藏哈~

上一篇:Thinkphp5.1 分页之后搜索带上参数


下一篇:Django3中分组查询的一个坑