马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
前言
19届同学反馈,拼夕夕的数据分析SQL部分出的很好,20届感叹道,说的太对了。 拼夕夕的笔试题一共五道,考试时只划分为一个部分,答题时间90分钟。 第一部分:SQL查询题3道第一题:活动运营数据分析 表1——订单表orders,大概字段有(user_id‘用户编号’, order_pay‘订单金额’ , order_time‘下单时间’)。 表2——活动报名表act_apply,大概字段有(act_id‘活动编号’, user_id‘报名用户’,act_time‘报名时间’) 需求: 1. 统计每个活动对应所有用户在报名后产生的总订单金额,总订单数。(每个用户限报一个活动,题干默认用户报名后产生的订单均为参加活动的订单)。 2. 统计每个活动从开始后到当天(考试日)平均每天产生的订单数,活动开始时间定义为最早有用户报名的时间。(涉及到时间的数据类型均为:datetime)。 第1问: [AppleScript] 纯文本查看 复制代码 select t2.act_id,count(t1.order_time) as num_order,sum(order_pay) as sum_order as num[/color][/size][/font][/align][font=微软雅黑][size=3][color=#000000]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问: [AppleScript] 纯文本查看 复制代码 select act_id,avg()
from (
select *
from orders
)t1
inner join (
select *
from act_apply
)t2
group by 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操作必须相邻。 [AppleScript] 纯文本查看 复制代码 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()可以轻易的实现! [AppleScript] 纯文本查看 复制代码 select date(log_time),count(distinct user_id) --感谢评论区 指正@七秒记忆的懒鱼 @宣禾火 [/color][/size][/font][/align][font=微软雅黑][size=3][color=#000000]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天的回访比例 思路: 如何定义新增用户:用户登陆表中最早的登陆时间所在的用户数为当天新增用户数; 第2天回访用户数:第一天登陆的用户中,第二天依旧登陆的用户;--次日留存率 第30天的回访用户数:第一天登陆用户中,第30天依旧登陆的用户; [AppleScript] 纯文本查看 复制代码 select date(t1.user_begin),count(distinct t1.user_id) as '新增用户' ,count(distinct t2.user_id) as '第二日留存用户',[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]count(distinct t3.user_id) as '第30日留存用户'[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]from ([/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]select user_id,min(log_time) as user_begin[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]from user_log[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]group by user_id[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000])t1 --感谢评论区指正@Rey[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]left join [/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]([/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]select user_id,log_time[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]from user_log[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000])t2[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]on t1.user_id=t2.user_id[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]and date(t2.log_time)=date(t1.user_begin)+1[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]left join ([/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]select user_id,log_time[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]from user_log[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000])t3[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]on t1.user_id=t3.user_id[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]and date(t3.log_time)=date(t1.user_begin)+29[/color][/size][/font][/align]
[align=left][font=微软雅黑][size=3][color=#000000]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/次) 第三部分:综合分析题1道 ABtest 某网站优化了商品详情页,现在新旧两个版本同时运行,新版页面覆盖了10%的用户,旧版覆盖90%的用户。现在需要了解,新版页面是否能够提高商品详情页到支付页的转化率,并决定是否要覆盖旧版,你能为决策提供哪些信息,需要收集哪些指标,给出统计方法及过程。 解答: 使用A/B测试模型,分析两个版本在一段时间期限内,详情页面到支付页面的转化率变化,并计算转化率变化后引起的的GMV变化。 可选择的决策:①确定发布新版本;②调整分流比例继续测试;③优化迭代方案重新开发。 要统计的指标:期限内新、旧版本商品详情页到支付页转化率 ,支付金额。 要衡量的指标:转化率变化 t 在是可接受的置信区间内是否显著,同时参考收益提升率。 指标计算方法:转化率=从某详情页到支付页用户数/浏览该商品详情页用户数(取日平均和标准差) 支付金额=从某详情页到支付页到支付成功路径用户的本次支付金额(取日平均) 采用决策①的情况:本次页面改进在显著性水平内,证明了‘转化率提升的假设’。并且收益提升率达到预期水平。 采用决策②的情况:本次页面改进在显著性水平内,无法证明‘转化率提升的假设’。分析原因可能是新版本样本空间不足。 采用决策③的情况:本次页面改进在显著性水平内,证明了‘转化率提升的假设’。但是收益提升率没有达到预期水平。 文章整理自知乎,作者「求知鸟」
|