互聯(lián)網(wǎng)公司數(shù)據(jù)分析SQL應(yīng)聘面試筆試題

互聯(lián)網(wǎng)公司數(shù)據(jù)分析SQL應(yīng)聘面試筆試題（真題）第一部分：SQL查詢題3道第一題：活動運(yùn)營數(shù)據(jù)分析表1 訂單表orders，大概字段有（user_id'用戶編號'，order_pay‘訂單金額’，order_time'下單時(shí)間’）。表2——活動報(bào)名表act_apply，大概字段有（act_id'活動編號’，user_id'報(bào)名用戶‘，act_time報(bào)名時(shí)間’）需求：.統(tǒng)計(jì)每個活動對應(yīng)所有用戶在報(bào)名后產(chǎn)生的總訂單金額，總訂單數(shù)。（每個用戶限報(bào)一個活動，題干默認(rèn)用戶報(bào)名后產(chǎn)生的訂單均為參加活動的訂單）。.統(tǒng)計(jì)每個活動從開始后到當(dāng)天（考試日）平均每天產(chǎn)生的訂單數(shù)，活動開始時(shí)間定義為最早有用戶報(bào)名的時(shí)間。（涉及到時(shí)間的數(shù)據(jù)類型均為：datetime）。第1問：selectt2.act_id,count（t1.order_time）asnum_order,sum（order_pay）assum_orderasnufrom（selectuser_id,order_pay,order_timefromorders）t1innerjoin（selectuser_id,act_id,act_timefromact_apply)t2ont1.user_id=t2.user_idwheret1.order_time>=t2.act_timegroupbyt2.act_id第2問：selectt1.act_id,count(order_time)/datediff(now(),t1.begin_time)from(selectact_id,user_id,act_time,min(act_time)over(partitionbyact_id)asbegin_timefromact_apply)t1innerjoin(selectuser_id,order_timefromorders)t20nt1.user_id=t2.user_idwheret1.act_timebetweent1.begin_timeandnow()andt2.order_time>=t1.act_time--補(bǔ)充條件@Ethangroupbyt1.act_id說明：Over函數(shù)可以和其它聚集函數(shù)、分析函數(shù)搭配，起到不同的作用。例如這里的SUM，max，min，avg，還有諸如Rank，Dense_rank等。(mysql支持，hive也支持)第二題：用戶行為分析表1 用戶行為表tracking_log，大概字段有(user_id'用戶編號’,opr_id'操作編號'，log_time'操作時(shí)間’)需求：1、計(jì)算每天的訪客數(shù)和他們的平均操作次數(shù)。2、統(tǒng)計(jì)每天符合以下條件的用戶數(shù)：A操作之后是B操作，AB操作必須相鄰。electdate(log_time),count(distinctuser_id),avg(num_ci)from(selectdate(log_time),user_id,count(opr_id)asnum_cifromtracking_loggroupbyuser_id,date(log_time))groupbydate(log_time)第2問：在第一問的基礎(chǔ)上添加了限制：A操作之后是B操作，AB操作必須相鄰。lead()over()可以輕易的實(shí)現(xiàn)！selectdate(log_time),count(distinctuser_id)--感謝評論區(qū)指正@七秒記憶的懶魚@宣禾火from(selectuser_id,date(log_time),opr_id,lead(opr_id,1)over(partitionbyuser_idorderbylog_time)asopr_id_2fromtracking_log)whereopr_id='A'andopr_id_2='B'groupbydate(log_time)第三題：用戶新增留存分析表1——用戶登陸表user_log，大概字段有(user_id'用戶編號’，log_time'登陸時(shí)間’)要求：每天新增用戶數(shù)，以及他們第2天、30天的回訪比例如何定義新增用戶：用戶登陸表中最早的登陸時(shí)間所在的用戶數(shù)為當(dāng)天新增用戶數(shù)；第2天回訪用戶數(shù)：第一天登陸的用戶中，第二天依舊登陸的用戶；--次日留存率第30天的回訪用戶數(shù)：第一天登陸用戶中，第30天依舊登陸的用戶selectdate(t1.user_begin),count(distinctt1.user_id)as'新增用戶',count(distinctt2.user_id)as'第二日留存用戶'，count(distinctt3.user_id)as'第30日留存用戶'from(selectuser_id,min(log_time)asuser_beginfromuser_loggroupbyuser_id)t1--感謝評論區(qū)指正@Reyleftjoin(selectuser_id,log_timefromuser_log)t20nt1.user_id=t2.user_idanddate(t2.log_time)=date(t1.user_begin)+1leftjoin(selectuser_id,log_timefromuser_log)t30nt1.user_id=t3.user_idanddate(t3.log_time)=date(t1.user_begin)+29groupbydate(t1.user_begin)第二部分：計(jì)算題1道貝葉斯公式的應(yīng)用已知A,B廠生產(chǎn)的產(chǎn)品的次品率分別是1%和2%，現(xiàn)在由A,B產(chǎn)品分別占60%、40%的樣品中隨機(jī)抽一件，若取到的是次品，求此次品是B廠生產(chǎn)的概率。已知：P(A)=0.6,P(B)=0.4,P(次/A)=0.0LP(次/B)=0.02求：P(B/次)P(B/次尸P(次/B)P(B)P(B/次尸第三部分：綜合分析題1道ABtest某網(wǎng)站優(yōu)化了商品詳情頁，現(xiàn)在新舊兩個版本同時(shí)運(yùn)行，新版頁面覆蓋了10%的用戶，舊版覆蓋90%的用戶?，F(xiàn)在需要了解，新版頁面是否能夠提高商品詳情頁到支付頁的轉(zhuǎn)化率，并決定是否要覆蓋舊版，你能為決策提供哪些信息，需要收集哪些指標(biāo)，給出統(tǒng)計(jì)方法及過程。解答：使用A/B測試模型，分析兩個版本在一段時(shí)間期限內(nèi)，詳情頁面到支付頁面的轉(zhuǎn)化率變化，并計(jì)算轉(zhuǎn)化率變化后引起的的GMV變化?？蛇x擇的決策：①確定發(fā)布新版本；②調(diào)整分流比例繼續(xù)測試；③優(yōu)化迭代方案重新開發(fā)。要統(tǒng)計(jì)的指標(biāo)：期限內(nèi)新、舊版本商品詳情頁到支付頁轉(zhuǎn)化率，支付金額。要衡量的指標(biāo)：轉(zhuǎn)化率變化t在是可接受的置信區(qū)間內(nèi)是否顯著，同時(shí)參考收益提升率。指標(biāo)計(jì)算方法:轉(zhuǎn)化率二從某詳情頁到支付頁用戶數(shù)/瀏覽該商品詳情頁用戶數(shù)（取日平均和標(biāo)準(zhǔn)差）支付金額=從某詳情頁到支付頁到支付成功路徑用戶的本次支付金額（取日平均）采用決策