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

2020-07-29 本文已影响0人 kaka22

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

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

第一部分：SQL查询题3道

问题一：

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

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

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

要求：

统计每个活动对应所有用户在报名后产生的总订单金额，总订单数。（每个用户限报一个活动,题干默认用户报名后产生的订单均为参加活动的订单）。
统计每个活动从开始后到当天（考试日）平均每天产生的订单数，活动开始时间定义为最早有用户报名的时间。（涉及到时间的数据类型均为：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 的问题先学习一下再返回来看这个问题