写在前面
数据分析SQL笔试题系列第4篇来啦！之前笔试题的文章如果没有看可以戳：
无论你是刚毕业的职场小鲜肉、还是想转行数据分析的小白玩家，只要想进入数据分析的行业，都逃不过数据分析面试的考验，这里最重要也是最关键的一关就是SQL笔试了，不过不用担心，结合作者6年+的工作和面试经验，系统全面地整理了数据分析面试中那些高频出现的以及各大厂的SQL笔试题，学习了这些笔试题的常见套路和解法，把这些题目都刷一遍，在接下来的笔试中应该可以一往无前，收割offer啦！
SQL笔试不仅考查代码的熟练程度，更多的考查的是对业务的理解程度，只有理解了业务背景和逻辑，才能更快速准确地给出答案。本篇选取了PDD的业务笔试题，在某个业务背景下考查SQL能力，赶紧来看看这些题目你都会么？
用户行为分析
业务背景
某购物APP最近上线了一个新功能，用户签到后可以跳转到大转盘抽奖，抽奖获得的奖金可以抵消购物的费用，以此来培养用户使用app的习惯。
数据表介绍
现有一张用户行为表user_log，主要字段如下，记录了用户在app上的所有行为日志，即何人user_id在何时event_time进行了什么操作event_id。
需求：
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‘下单时间’）。
活动报名表act_join，大概字段有（act_id‘活动编号’, user_id‘报名用户’,join_time‘报名时间’）
需求：
- 统计每个活动报名的所有用户在报名后产生的总订单金额，总订单数。（每个用户限报一个活动,且默认用户报名后产生的订单均为参加活动的订单）。
- 统计每个活动从开始后到当天（数据统计日）平均每天产生的订单数，活动开始时间定义为最早有用户报名的时间。
思路：
第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篇文章的内容，部分历史文章请回翻公众号，更多数据分析面试笔试的文章持续更新中，敬请期待，如果觉得不错，也欢迎分享、点赞和收藏哈~