拼多多学霸批数据分析第二批笔试题

本次笔试一共五个题，3道SQL，1个概率题，1个abtest

1. 给一个表ord，包括user_id(用户id)，goods_id(商品id），goods_num（购买商品数），ord_amt（购买金额），creat_time（购买时间）

求最近30天商品的平均价格

select goods_id as '商品id',sum(ord_amt)/sum(goods_num) as '近30天平均价格' from (select goods_id,sum(ord_amt),sum(goods_num) from ord) where datediff(convert(new(),date),convert(creat_time,date))<30 group by goods_id

2. 两个表：

pv_log(用户浏览记录表）：pv_id(页面id），user_id(用户id），creat_time(访问时间)

dimuser(用户注册记录表）：user_id(用户id），age(年龄）,creat_time(注册时间）

统计浏览不同页面数的用户对应的平均年龄

--考虑了访问时间和注册时间的年龄间隔
select a.pv_id,AVG(a.newyear-b.birthyear)
from
(select pv_id,user_id,year(creat_time) as newyear 
from pv_log
group by ov_id,user_id,year(creat_time)
)a
left join 
(select user_id,(year(creat_time)-age) as birthyear
from dim_user
group by user_id
)b
on a.user_id = b.user_id;

3. 两张表

tbl_ordr(用户订单表）：user_id（用户id）, ordr_id（订单号）, ordr_goods（订单商品id）, ordr_time（预定时间）

tblclk(用户商品点击明细表）：clk_id(点击id）, user_id, clk_time(点击时间）, clk_goods（点击的商品id，和ordr_goods对应）

用户点击商品之后的订单算是这次点击产生的订单；多次点击后产生的订单，算订单创建前最后一次点击产生的订单，求有订单商品的点击及订单号

--将订单表和点击商品明细表关联，找出下单商品所有的记录
with base0 as
(
select a.usr_id,a.ordr_id,a.ord_goods,b.clk_id,max(b.clk_time) over(partition by clk_goods)
from tbl_order a
left join tbl_clk b
on a.usr_id = b.usr_id and a.ord_goods = b.clk_goods
where b.clk_time < a.ordr_time
)
select clk_id,order_id from base0

4. 10个白球1个黑球，谁先取出黑球获胜，AB轮流取，取出后不放回，A先取，求A、B各自获胜概率

A第一次取出黑球的概率：1/11

A第二次取出黑球的概率：10/11*9/10*1/9=1/11

A第三次取出黑球的概率：10/11*9/10*8/9*7/8*1/7=1/11

A第四次取出黑球的概率：10/11*9/10*8/9*7/8*6/7*5/6*1/5=1/11

A第五次取出黑球的概率：10/11*9/10*8/9*7/8*6/7*5/6*4/5*3/4*1/3=1/11

A第六次取出黑球的概率：10/11*9/10*8/9*7/8*6/7*5/6*4/5*3/4*2/3*1/2*1=1/11

B第一次取出黑球的概率：10/11*1/10=1/11

B第二次取出黑球的概率：10/11*9/10*8/9*1/8=1/11

B第三次取出黑球的概率：10/11*9/10*8/9*7/8*6/7*1/6=1/11

B第四次取出黑球的概率：10/11*9/10*8/9*7/8*6/7*5/6*4/5*1/4=1/11

B第五次取出黑球的概率：10/11*9/10*8/9*7/8*6/7*5/6*4/5*3/4*2/3*1/2=1/11

所以，A取出黑球胜利的概率为6/11，B取出黑球胜利的概率为5/11

5. 发优惠券，一种满20减2，一种满30减3

1）不考虑券的成本，评判两种方案优劣

2）考虑券的成本评判