前言
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問：
<code>select t2.act_id,count(t1.order_time) as num_order,sum(order_pay) as sum_order as nufrom (select user_id,order_pay,order_time from orders)t1inner join (select user_id,act_id,act_timefrom act_apply)t2on t1.user_id=t2.user_idwhere t1.order_time>=t2.act_timegroup by t2.act_id/<code>
第2問：
<code>select t1.act_id,count(order_time)/datediff(now(),t1.begin_time)
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_timefrom 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/<code>
說明：Over函數可以和其它聚集函數、分析函數搭配，起到不同的作用。例如這裡的SUM，max，min，avg，還有諸如Rank，Dense_rank等。(mysql支持，hive也支持)
第二題：用戶行為分析
表1——用戶行為表tracking_log，大概字段有（user_id‘用戶編號’,opr_id‘操作編號’,log_time‘操作時間’）
需求：
1、計算每天的訪客數和他們的平均操作次數。
2、統計每天符合以下條件的用戶數：A操作之後是B操作，AB操作必須相鄰。
<code>elect 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)/<code>
第2問：
在第一問的基礎上添加了限制：A操作之後是B操作，AB操作必須相鄰。
lead（）over（）可以輕易的實現！
<code>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)/<code>
第三題：用戶新增留存分析
表1——用戶登陸表user_log，大概字段有（user_id‘用戶編號’，log_time‘登陸時間’）
要求：
每天新增用戶數，以及他們第2天、30天的回訪比例
如何定義新增用戶：用戶登陸表中最早的登陸時間所在的用戶數為當天新增用戶數；
第2天回訪用戶數：第一天登陸的用戶中，第二天依舊登陸的用戶；--次日留存率
第30天的回訪用戶數：第一天登陸用戶中，第30天依舊登陸的用戶
<code>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_loggroup by user_id)t1 --感謝評論區指正@Rey
left join(select user_id,log_timefrom user_log)t2
on t1.user_id=t2.user_idand date(t2.log_time)=date(t1.user_begin)+1
left join (select user_id,log_timefrom user_log)t3
on t1.user_id=t3.user_idand date(t3.log_time)=date(t1.user_begin)+29
group by date(t1.user_begin)/<code>
第二部分：計算題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 在是可接受的置信區間內是否顯著，同時參考收益提升率。
指標計算方法：轉化率=從某詳情頁到支付頁用戶數/瀏覽該商品詳情頁用戶數（取日平均和標準差）
支付金額=從某詳情頁到支付頁到支付成功路徑用戶的本次支付金額（取日平均）
採用決策①的情況：本次頁面改進在顯著性水平內，證明了‘轉化率提升的假設’。並且收益提升率達到預期水平。
採用決策②的情況：本次頁面改進在顯著性水平內，無法證明‘轉化率提升的假設’。分析原因可能是新版本樣本空間不足。
採用決策③的情況：本次頁面改進在顯著性水平內，證明了‘轉化率提升的假設’。但是收益提升率沒有達到預期水平。
-- The End--
閱讀更多 求知鳥 的文章
