转载自https://www.cnblogs.com/hider/p/12642452.html

 https://blog.csdn.net/SeizeeveryDay/article/details/105424385 

第一部分：SQL查询

一、活动运营数据分析

表1：订单表 orders，大概字段有（user_id‘用户编号’, order_pay‘订单金额’ , order_time‘下单时间’）

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

需求：

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

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

解答：

-- 1.需求一 select b.act_id, count(a.order_time) as num_order, sum(a.order_pay) as sum_order from ( select user_id, order_pay, order_time from orders ) a inner join ( select user_id, act_id, act_time from act_apply ) b on a.user_id = b.user_id where a.order_time >= b.act_time group by b.act_id；

 说明：一定要审清题意，题中说的是orders表“大概”有user_id,order_pay,order_time三个字段，所以一定要使用嵌套子查询。同时需要注意的是 统计每个活动的用户在“”报名后“产生的总订单数、订单金额，所有这块涉及到了 时间比较 订单时间要晚于活动报名时间。

-- 2.需求二 select a.act_id, count(order_time)/datediff(now(), a.begin_time) from ( select act_id, user_id, act_time, min(act_time) over (partition by act_id) as begin_time from act_apply ) a inner join ( select user_id, order_time from orders ) b on a.user_id = b.user_id where a.act_time between a.begin_time and now() and b.order_time >= a.act_time -- 补充添加：订单支付时间 ≥ 活动报名时间 group by a.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操作必须相邻。

-- 3.需求三 select date(log_time), count(distinct user_id) as user_cnt, avg(num_cnt) as avg_cnt from ( select date(log_time), user_id, count(opr_id) as num_cnt from tracking_log group by date(log_time), user_id ) group by date(log_time)；

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

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

-- 4.需求四 用户、操作、时间 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) -- lag 滞后 让数据向后移动 -- lead 超前 让数据向前移动

 三、用户新增留存分析

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

需求：

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

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

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

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

-- 5.需求五 用户、登录时间 select date(a.user_begin), count(distinct a.user_id) as '新增用户', count(distinct b.user_id) as '第2日留存用户', count(distinct c.user_id) as '第30日留存用户' from ( select user_id, min(log_time) as user_begin from user_log group by user_id ) a left join ( select user_id, log_time from user_log ) b on a.user_id = b.user_id and date(b.log_time) = date(a.user_begin) + 1 left join ( select user_id, log_time from user_log ) c on a.user_id = c.user_id and date(c.log_time) = date(a.user_begin) + 29 group by date(a.user_begin)；

此处的 date 日期函数有待商榷，日常oracle中使用 trunc() 函数即可截取，hive中使用to_date() 亦可。

第二部分：计算器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 在是可接受的置信区间内是否显著，同时参考收益提升率。

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

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

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

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

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

使用AARR模型分析。