【数据分析面试】大厂高频SQL笔试题(三)
发布网友
发布时间：2024-09-29 20:22
共1个回答
热心网友
时间：2024-11-17 20:55
无论你是刚毕业的职场小鲜肉、还是想转行数据分析的小白玩家，只要想进入数据分析的行业，都逃不过数据分析面试的考验，这里最重要也是最关键的一关就是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就会在表中产生对应的行为日志，所以每天的访客数只需要按天对用户数去重即可，每天人均行为次数的计算，因为一次行为就会产生一条记录，所以，人均行为次数就是所有的记录计数，除以总的访客数。
第2问升级了难度，虽然也是统计用户数，但是添加了*：签到之后要大转盘抽奖，两个行为一前一后必须相邻才可以。这个时候我们可以用窗口函数的位移函数lead()over()实现，lead可以取当前记录的下一条记录，如果我们对每个用户user_id分组，按照行为时间event_time升序排列，就可以得到一个用户的连续的行为记录，再用lead()就可以得到下一条记录，从而在当前记录中得到下一条记录，对两个连续行为进行筛选，就可以计算满足这个条件的用户数。
为了提高某店铺的营业额，最近运营部门上线了多个运营活动，用户参与活动后可以领取不定额的优惠券，以此鼓励用户下单。但每个用户限参与一个活动，不可同时参与多个活动。
现有一张订单表orders和活动报名表act_join,分别记录了用户的下单明细和用户报名的活动明细。具体字段如下：
订单表orders，大概字段有（user_id‘用户编号',order_id '订单编号' ，order_sales‘订单金额’ , order_time‘下单时间’）。
活动报名表act_join，大概字段有（act_id‘活动编号', user_id‘报名用户',join_time‘报名时间’）
需求：1. 统计每个活动报名的所有用户在报名后产生的总订单金额，总订单数。（每个用户限报一个活动,且默认用户报名后产生的订单均为参加活动的订单）。
2. 统计每个活动从开始后到当天（数据统计日）平均每天产生的订单数，活动开始时间定义为最早有用户报名的时间。
思路：第1问计算总订单金额和总订单数，这两个指标都比较简单sum(order_sales)、count(order_id)就可以，但是关键在于限定条件，是每个活动报名后的用户的汇总，必须是报名了某个活动，且必须在活动开始后的数据统计。可以通过订单表orders和报名表act_join连接，限定订单时间大于等于活动的开始时间即可。
第2问与第1问有相似之处，同样是用户报名后的下单，只是多了一些限定条件：同时要满足要小于等于计算日期当天，也就是程序运行的系统时间now()，在此基础上，计算整体的订单数，除以活动进行的天数，就是该活动每天的平均下单数。
说明：这里使用了窗口函数，计算了每个活动的开始时间，然后join 订单表，通过where条件将上面的限定条件满足。当然这只是一种解题思路，还有很多其他的解法，大家可以尝试。
上面通过2个实际的业务场景，不仅考查了SQL的代码能力，更主要的是考查了大家对业务场景的理解能力，如果理解不了业务场景，也就很难写出SQL代码，相反，如果业务场景非常熟悉，代码就比较简单了，所以，我们在练习SQL的时候，一定要结合着业务场景来练，这样才比较贴合实际业务场景，也更能在笔试中有余，脱颖而出啦！
