更多数据分析思维，工具和实际项目干货文章，请移步共粽号：【数据分析星球】，还有数分资料包领取！

01 写在前面

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

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

SQL笔试不仅考查代码的熟练程度，更多的考查的是对业务的理解程度，只有理解了业务背景和逻辑，才能更快速准确地给出答案。本篇选取了PDD的业务笔试题，在某个业务背景下考查SQL能力，赶紧来看看这些题目你都会么？

02 用户行为分析

业务背景

某购物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;

03 活动效果数据分析

业务背景

为了提高某店铺的营业额，最近运营部门上线了多个运营活动，用户参与活动后可以领取不定额的优惠券，以此鼓励用户下单。但每个用户限参与一个活动，不可同时参与多个活动。

数据表介绍

现有一张订单表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连接，限定订单时间大于等于活动的开始时间即可。

代码如下：

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篇文章的内容，部分历史文章请回翻公众号，更多数据分析面试笔试的文章持续更新中，敬请期待，如果觉得不错，也欢迎分享、点赞和收藏哈~