笔试题目(一)——2020学霸批拼多多数据分析笔试总结

引用： 2020学霸批拼多多数据分析笔试总结
拼多多2020学霸批数据分析师笔试 —— SQL整理

参考这个里边的题目 自己试着做一下 看看自己sql的水平怎么样

第一部分：SQL查询题3道

问题一：

（考点：活动运营数据分析）

表1——订单表orders，大概字段有（user_id‘用户编号’, order_pay‘订单金额’ , order_time‘下单时间’）。(图中字段名有些差异)

表2——活动报名表act_apply，大概字段有（act_id‘活动编号’, user_id‘报名用户’,act_time‘报名时间’）

要求：

1. 统计每个活动对应所有用户在报名后产生的总订单金额，总订单数。（每个用户限报一个活动,题干默认用户报名后产生的订单均为参加活动的订单）。

2. 统计每个活动从开始后到当天（考试日）平均每天产生的订单数，活动开始时间定义为最早有用户报名的时间。（涉及到时间的数据类型均为：datetime）。

解答

第一问

select A.act_id, sum(O.order_pay) as total_pay, count(O.user_id) as order_count
from orders as O
left join act_apply as A
on A.user_id = O.user_id
group by A.act_id

存在的问题： 少考虑了一个因素 订单时间应当超过活动时间 且别名起成中文会好一些

select A.act_id as '活动编号', sum(O.order_pay) as '总金额', count(O.user_id) as '订单数'
from orders as O
left join act_apply as A
on A.user_id = O.user_id
where A.act_time  <= O.order_time
group by A.act_id;

第二问

先选出每个活动开始的时间

select A.act_id, min(A.act_time) as 'act_start' 
from act_apply as A
group by A.act_id;

第一问可以得到总订单数 两表连接

select tmp1.act_id as  '活动编号', tmp1.order_num/datediff(now(), tmp2.'act_start' ) as '活动开始后平均每天下单数'
from (select A.act_id , count(O.user_id) as 'order_num'
from orders as O
left join act_apply as A
on A.user_id = O.user_id
where A.act_time  <= O.order_time
group by A.act_id) as tmp1
join (select A.act_id, min(A.act_time) as 'act_start' 
from act_apply as A
group by A.act_id) as tmp2
on tmp1.act_id = tmp2.act_id

整体思路就是 两个子查询 第一个先查出每个活动的订单数量 第二个查出每个订单的开始日期 两表按照活动id连接 每个订单的数量除以 当前日期与开始日期的差值即可
(应该是对的)

后边发现自己的写法写复杂了 其实第二个子查询是没必要的

select A.act_id as  '活动编号', count(O.user_id)/datediff(now(), min(A.act_time))  as '活动开始后平均每天下单数' 
from orders as O
left join act_apply as A
on A.user_id = O.user_id
where A.act_time  <= O.order_time
group by A.act_id

作者的答案 其实也写复杂了但是可以学习一下窗口函数

SELECT act_id as '活动编号', COUNT(*)/DATEDIFF(NOW(),act_start) AS '活动开始后平均每天下单数'
FROM orders a
LEFT JOIN
(SELECT user_id ,act_id ,act_time, min(act_time) over(PARTITION by act_id) as 'act_start'
FROM act_apply
) b
ON a.user_id=b.user_id
WHERE order_time>=act_time
GROUP BY act_id,act_start

这里用到了一个over函数 具体用法在后边进行了补充
这个的思路与第一问的思路一致 只不过对于活动表给出了每个活动的开始日期(使用over比较方便 否则需要group by min 然后再做一次连接) 然后两表连接 分组计数除以时间差即可
需要注意的是 我也觉得最后一行GROUP BY act_id,act_start 可以改为 GROUP BY act_id 但是作者给出了解释:

如果group by 没有act_start的话 sql不允许select语句使用它的～是聚合函数的限制

补充
https://kknews.cc/code/66jnqzv.html
OVER的定义
OVER用于为行定义一个窗口，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

OVER的语法

OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] )

PARTITION BY 子句进行分组；
ORDER BY 子句进行排序。
窗口函数OVER()指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。
开窗函数不需要使用GROUP BY就可以对数据进行分组，还可以同时返回基础行的列和聚合列。

OVER的用法
OVER开窗函数必须与聚合函数或排序函数一起使用，聚合函数一般指SUM(),MAX(),MIN,COUNT(),AVG()等常见函数。排序函数一般指RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE()等。

具体例子可以参考以上链接

问题二：

（考点：用户行为路径分析）

表1——用户行为表tracking_log，大概字段有（user_id‘用户编号’,opr_id‘操作编号’,log_time‘操作时间’）

要求：
1.计算每天的访客数和他们的平均操作次数。
2.统计每天符合以下条件的用户数：A操作之后是B操作，AB操作必须相邻。

解答
问题1：
先选出每天的访客数和每天的操作次数

select T.log_time, count(distinct T.user_id) as '访客数', count(T.opr_id) as '总操作次数'
from tracking_log as T
group by T.log_time

子查询

select tmp.log_time, tmp.'访客数', tmp.'总操作次数'/tmp.'访客数' as '平均操作次数'
from (select T.log_time, count(distinct T.user_id) as '访客数', count(T.opr_id) as '总操作次数'
from tracking_log as T
group by T.log_time) as tmp;

好像又写复杂了。。 不用子查询就可以完成

select T.log_time, count(distinct T.user_id) as '访客数', count(T.opr_id)/count(distinct T.user_id) as '平均操作次数'
from tracking_log as T
group by T.log_time

参考答案

SELECT b.date ,  COUNT(b.user_id) as '访客数' , AVG(op) AS '平均操作次数'
FROM
(SELECT user_id , COUNT(opr_type) as 'op', CONVERT(log_time,date) as 'date' FROM tracking_log
GROUP BY user_id,date) b
GROUP BY b.date

思路：用group嵌套，刚好可以把人数和人次分解开
子查询对日期和user_id进行分组 得到每天每个人的操作次数
然后对日期进行分组 统计user_id的数量即为每天的访客数 对每天每个人的操作次数取平均即为平均操作次数

需要注意的点:
MySQL 的CAST()和CONVERT()函数可用来获取一个类型的值，并产生另一个类型的值。两者具体的语法如下：

CAST(value as type);
CONVERT(value, type);

这里的log_time需要用 CONVERT(log_time,date) as 'date' 处理

而且mysql的一个特点是group by 中的字段 是可以使用 select中的别名的

MySQL extends standard SQL to permit noncolumn expressions in GROUP BY clauses and considers the preceding statement valid.

修改自己的答案

select CONVERT(T.log_time,date) , count(distinct T.user_id) as '访客数', count(T.opr_id)/count(distinct T.user_id) as '平均操作次数'
from tracking_log as T
group by CONVERT(T.log_time,date) 

问题2:
刷过leetcode之后这个题还是比较简单的 就是写起来有些复杂
先对时间排序对于不同的user_id创建按时间排序的rank

select T.user_id， CONVERT(T.log_time,date) as date， T. opr_id,
@rank:= if(T.user_id = @pre_id,  @rank+1, 1)，
@pre_id:= T.user_id 
from tracking_log as T, (select @rank:=0, @pre_id:=NULL) as init
-- Mysql,order by 后跟 别名 表达式 均可
order by T.user_id, CONVERT(T.log_time,date) asc;

然后两个上表进行连接 连接条件是user_id相等 tmp1.rank = tmp2.rank-1 (其实就是对于同一个user_id通过rank后后一天进行连接)
然后筛选tmp1.opr_id = 'A' tmp2.opr_id = 'B'
最后对日期分组计数即可 可能一天中用户先A后B多次 但只需要统计1次 因此需要去重

select tmp1.date, count(distinct tmp1.user_id) as 'A-B路径用户计数' 
from (select T.user_id， CONVERT(T.log_time,date) as date， T. opr_id,
@rank:= if(T.user_id = @pre_id,  @rank+1, 1) as rank，
@pre_id:= T.user_id 
from tracking_log as T, (select @rank:=0, @pre_id:=NULL) as init
order by T.user_id, CONVERT(T.log_time,date) asc) as tmp1
join (select T.user_id， CONVERT(T.log_time,date) as date， T. opr_id,
@rank:= if(T.user_id = @pre_id,  @rank+1, 1) as rank，
@pre_id:= T.user_id 
from tracking_log as T, (select @rank:=0, @pre_id:=NULL) as init
order by T.user_id, CONVERT(T.log_time,date) asc)  as tmp2
on tmp1.rank = tmp2.rank - 1 and tmp1.user_id = tmp2.user_id
where tmp1.opr_id = 'A'  and tmp2.opr_id = 'B'
group by tmp1.date;

参考答案

ELECT a.Date , COUNT(*) as 'A-B路径用户计数'
FROM(
SELECT DISTINCT user_id as 'User',opr_type as '1st', CONVERT(log_time,date) as 'Date', lead(opr_type,1) 
over(PARTITION by user_id,CONVERT(log_time,date) ORDER BY log_time) as '2nd'
FROM tracking_log
) a
WHERE a.1st = 'A' and a.2nd ='B'
GROUP BY a.Date

确实利用窗口函数要简单很多
对user_id,和时间分组 按时间排序 取出后一个操作opr_id的写法是这样的
lag(1) 后移一项 lead(1)前移一项 这里应该用 lead

select lead(T.opr_id) over( PARTITION by T.user_id,convert(T.log_time,date) order by T.log_time asc)
from tracking_log as T

整体选出是这样的 这里也需要去重

SELECT DISTINCT T.user_id as 'User', T.opr_type as '1st', CONVERT(T.log_time,date) as 'Date', lead(T.opr_type,1) 
over(PARTITION by T.user_id, CONVERT(T.log_time,date) ORDER BY CONVERT(T.log_time,date) ASC) as '2nd'
FROM tracking_log as T

最后剩下就是分组筛选 然后计数了

问题三

（考点：用户新增留存分析）

表1——用户登陆表user_log，大概字段有（user_id‘用户编号’，log_time‘登陆时间’）

要求：

1.每天新增用户数，以及他们第2天、30天的回访比例

参考答案：

(找出每个用户第一次登陆时间，再聚合时间得到每一天新增用户，时间要聚合到天)

解答
先找出每个用户的最小日期即为首次登陆的时间

select T.user_id, min(log_time) as first_time
from tracking_log as T
group by T.user_id

又要再筛选 用一下窗口函数试一下吧

select T.user_id, min(log_time) over(PARTITION by convert(T.user_id, date)) as first_time
from tracking_log as T

然后对first_time进行分组，统计去重的user_id个数即为当天的新用户数量

select tmp.first_time, count(distinct tmp.user_id) as new_count
from (select T.user_id, min(log_time) over(PARTITION by convert(T.user_id, date)) as first_time
from tracking_log as T) as tmp
group by tmp.first_time

第二天的回访数量怎么求
登陆时间与首次时间的差值为1就可以进行筛选

select tmp1.first_time, count(distinct tmp1.user_id) as 2nd_back
from (select T.user_id, min(log_time) over(PARTITION by convert(T.user_id, date)) as first_time, convert(T.user_id, date) as date
from tracking_log as T) as tmp1
where datediff(tmp1.date, tmp1.first_time) = 1
group by tmp1.first_time

同理 一个月后的回访

select tmp2.first_time, count(distinct tmp2.user_id) as 30nd_back
from (select T.user_id, min(log_time) over(PARTITION by convert(T.user_id, date)) as first_time, convert(T.user_id, date) as date
from tracking_log as T) as tmp2
where datediff(tmp2.date, tmp2.first_time) = 29
group by tmp2.first_time

以上三表进行连接 左连接

select a.first_time as '日期', a.new_count as '新增用户',
concat(round(100*b.2nd_back/a.new_count, 2), '%') as '第2天回访率'
concat(round(100*b.30nd_back/a.new_count, 2), '%') as '第30天回访率' 
from (select tmp.first_time, count(distinct tmp.user_id) as new_count
from (select T.user_id, min(log_time) over(PARTITION by convert(T.user_id, date)) as first_time
from tracking_log as T) as tmp
group by tmp.first_time) as a
left join (select tmp1.first_time, count(distinct tmp1.user_id) as 2nd_back
from (select T.user_id, min(log_time) over(PARTITION by convert(T.user_id, date)) as first_time, convert(T.user_id, date) as date
from tracking_log as T) as tmp1
where datediff(tmp1.date, tmp1.first_time) = 1
group by tmp1.first_time) as b
on a.first_time = b.first_time
left join (select tmp2.first_time, count(distinct tmp2.user_id) as 30nd_back
from (select T.user_id, min(log_time) over(PARTITION by convert(T.user_id, date)) as first_time, convert(T.user_id, date) as date
from tracking_log as T) as tmp2
where datediff(tmp2.date, tmp2.first_time) = 29
group by tmp2.first_time) as c
on a.first_time = c.first_time

第二部分：计算题1道

问题四：

（考点：贝叶斯公式的应用）

已知A,B厂生产的产品的次品率分别是1%和2%，现在由A,B产品分别占60%、40%的样品中随机抽一件，若取到的是次品，求此次品是B厂生产的概率。

第三部分：综合分析题1道

问题五：

（考点：产品更新决策的数据支持）

某网站优化了商品详情页，现在新旧两个版本同时运行，新版页面覆盖了10%的用户，旧版覆盖90%的用户。现在需要了解，新版页面是否能够提高商品详情页到支付页的转化率，并决定是否要覆盖旧版，你能为决策提供哪些信息，需要收集哪些指标，给出统计方法及过程。

这个是A/B test 的问题 先学习一下 再返回来看这个问题