「2020」拼多多数据分析笔试题 | 附解答

清疚 2023-02-23 12:29 126阅读 0赞

> 19届同学反馈，pdd的数据分析SQL部分出的很好，20届感叹道，说的太对了。

pdd的笔试题一共五道，考试时只划分为一个部分，答题时间90分钟。

## 第一部分：SQL查询题3道 ##

第一题：活动运营数据分析

表1——订单表*orders*，大概字段有（*user\_id‘用户编号’, order\_pay‘订单金额’ , order\_time‘下单时间’*）。

表2——活动报名表*act\_apply*，大概字段有（*act\_id‘活动编号’, user\_id‘报名用户’,act\_time‘报名时间’*）

**需求**：

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

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

**思路:**

> 有一个建议，在写SQL之前，请确保你完全理解了需求，如何确保你理解了需求了？  
> 在excel上写表格，枚举出几条记录，具体操作如下：

![format_png][]

select  t2.act_id,count(t1.order_time) as num_order,sum(order_pay) as sum_order as num
    from (
        select user_id,order_pay,order_time  from orders
    )t1
    inner join (
        select user_id,act_id,act_time
        from act_apply
    )t2
    on t1.user_id=t2.user_id
    where t1.order_time>=t2.act_time
    group by t2.act_id

第2问：

![format_png 1][]

由此表格，基本建立了SQL的框架：

select act_id,avg()
    from (
    select *
    from orders
    )t1
    inner join (
    select *
    from act_apply
    )t2
    group by act_id

然后，限制时间！在外部用where限制行！（同时在内部限制列---比如，最早时间）；**业务SQL的精华部分也就在于此了！！！**

select t1.act_id,count(order_time)/datediff(now(),min(t1.begin_time))--group by 聚合键，都得用到聚合函数
    from (
        select act_id, user_id,act_time,min(act_time) over(partition by act_id) as begin_time
        from act_apply
    )t1
    inner join (
        select user_id,order_time
        from orders
    )t2
    on t1.user_id=t2.user_id
    where t1.act_time between t1.begin_time and now()
    and t2.order_time >= t1.act_time --补充条件 @Ethan
    group by t1.act_id

说明：**Over函数可以和其它聚集函数、分析函数搭配，起到不同的作用。例如这里的SUM，max，min，avg，还有诸如Rank，Dense\_rank等。(mysql支持，hive也支持)**

第二题：用户行为分析

表1——用户行为表*tracking\_log*，大概字段有（*user\_id‘用户编号’,opr\_id‘操作编号’,log\_time‘操作时间’*）

**需求：**

1、计算每天的访客数和他们的平均操作次数。

2、统计每天符合以下条件的用户数：A操作之后是B操作，AB操作必须相邻。

**思路：**

![format_png 2][]

select date(log_time),count(distinct user_id),avg(num_ci)
    from (
        select date(log_time),user_id,count( opr_id) as num_ci
        from tracking_log
        group by user_id,date(log_time)
    )
    group by date(log_time)

第2问：

在第一问的基础上添加了限制：A操作之后是B操作，AB操作必须相邻。

lead（）over（）可以轻易的实现！

select date(log_time),count(distinct user_id) --感谢评论区 指正@七秒记忆的懒鱼 @宣禾火 
    from(
        select user_id,date(log_time),opr_id,lead(opr_id,1) over(partition by user_id order by log_time ) as opr_id_2
        from tracking_log
    )
    where opr_id='A' and opr_id_2='B'
    group by date(log_time)

第三题：用户新增留存分析

表1——用户登陆表*user\_log*，大概字段有（*user\_id‘用户编号’，log\_time‘登陆时间’*）

**要求：**

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

**思路：**

![format_png 3][]

如何定义新增用户：用户登陆表中最早的登陆时间所在的用户数为当天新增用户数；

第2天回访用户数：第一天登陆的用户中，第二天依旧登陆的用户；--次日留存率

第30天的回访用户数：第一天登陆用户中，第30天依旧登陆的用户；

select date(t1.user_begin),count(distinct t1.user_id) as '新增用户' ,count(distinct t2.user_id) as '第二日留存用户',
            count(distinct t3.user_id) as '第30日留存用户'
    from (
        select user_id,min(log_time) as user_begin
        from user_log
        group by user_id
    )t1  --感谢评论区指正@Rey
    left join 
    (
        select user_id,log_time
        from user_log
    )t2
    on t1.user_id=t2.user_id
    and date(t2.log_time)=date(t1.user_begin)+1
    left join (
        select user_id,log_time
        from user_log
    )t3
    on t1.user_id=t3.user_id
    and date(t3.log_time)=date(t1.user_begin)+29
    group by date(t1.user_begin)

## 第二部分：计算题1道 ##

贝叶斯公式的应用

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

已知：P(A)=0.6,P(B)=0.4,P(次/A)=0.01,P(次/B)=0.02

求：P(B/次)

![format_png 4][]

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

ABtest

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

解答：

使用A/B测试模型，分析两个版本在一段时间期限内，详情页面到支付页面的转化率变化，并计算转化率变化后引起的的GMV变化。

**可选择的决策：**①确定发布新版本；②调整分流比例继续测试；③优化迭代方案重新开发。

**要统计的指标：**期限内新、旧版本商品详情页到支付页转化率 ，支付金额。

**要衡量的指标：**转化率变化 t 在是可接受的置信区间内是否显著，同时参考收益提升率。

**指标计算方法**：转化率=从某详情页到支付页用户数/浏览该商品详情页用户数（取日平均和标准差）

支付金额=从某详情页到支付页到支付成功路径用户的本次支付金额（取日平均）

采用决策①的情况：本次页面改进在显著性水平内，证明了‘转化率提升的假设’。并且收益提升率达到预期水平。

采用决策②的情况：本次页面改进在显著性水平内，无法证明‘转化率提升的假设’。分析原因可能是新版本样本空间不足。

采用决策③的情况：本次页面改进在显著性水平内，证明了‘转化率提升的假设’。但是收益提升率没有达到预期水平。

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9waWMyLnpoaW1nLmNvbS84MC92Mi1kMjVlNGY3MmM5Y2JhZjg2M2YyZTIzOGI4Y2Q5NjRmMV83MjB3LnBuZw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9waWMzLnpoaW1nLmNvbS84MC92Mi0xNjY2NDBlZTExZjI2MWRhMmUwN2I3NmQwZDA2ZGYxZV83MjB3LmpwZw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9waWMzLnpoaW1nLmNvbS84MC92Mi1jMzcxZjgxMDNlNmFlNTFjN2JlZmNhY2U0MmY4OWJkNl83MjB3LnBuZw?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9waWMxLnpoaW1nLmNvbS84MC92Mi1mNTJhNjc3ZWI5NTkwOTY1ZDlkMWRlOGFlNzA5MDIyNF83MjB3LnBuZw?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9waWM0LnpoaW1nLmNvbS84MC92Mi0wZTg4OGYxZjc5YjVmODc3NjA3NzRjMWFmMjgwNDBiYl83MjB3LmpwZw?x-oss-process=image/format,png