筆試題目(一)——2020學(xué)霸批拼多多數(shù)據(jù)分析筆試總結(jié)

引用： 2020學(xué)霸批拼多多數(shù)據(jù)分析筆試總結(jié)
拼多多2020學(xué)霸批數(shù)據(jù)分析師筆試 —— SQL整理

參考這個(gè)里邊的題目自己試著做一下看看自己sql的水平怎么樣

第一部分：SQL查詢(xún)題3道

問(wèn)題一：

（考點(diǎn)：活動(dòng)運(yùn)營(yíng)數(shù)據(jù)分析）

表1——訂單表orders，大概字段有（user_id‘用戶(hù)編號(hào)’, order_pay‘訂單金額’ , order_time‘下單時(shí)間’）秕噪。(圖中字段名有些差異)

表2——活動(dòng)報(bào)名表act_apply斤斧，大概字段有（act_id‘活動(dòng)編號(hào)’, user_id‘報(bào)名用戶(hù)’,act_time‘報(bào)名時(shí)間’）

要求：

統(tǒng)計(jì)每個(gè)活動(dòng)對(duì)應(yīng)所有用戶(hù)在報(bào)名后產(chǎn)生的總訂單金額，總訂單數(shù)谣拣。（每個(gè)用戶(hù)限報(bào)一個(gè)活動(dòng),題干默認(rèn)用戶(hù)報(bào)名后產(chǎn)生的訂單均為參加活動(dòng)的訂單）募寨。
統(tǒng)計(jì)每個(gè)活動(dòng)從開(kāi)始后到當(dāng)天（考試日）平均每天產(chǎn)生的訂單數(shù)，活動(dòng)開(kāi)始時(shí)間定義為最早有用戶(hù)報(bào)名的時(shí)間森缠。（涉及到時(shí)間的數(shù)據(jù)類(lèi)型均為：datetime）拔鹰。

解答

第一問(wèn)

select A.act_id, sum(O.order_pay) as total_pay, count(O.user_id) as order_count
from orders as O
left join act_apply as A
on A.user_id = O.user_id
group by A.act_id

存在的問(wèn)題：少考慮了一個(gè)因素訂單時(shí)間應(yīng)當(dāng)超過(guò)活動(dòng)時(shí)間且別名起成中文會(huì)好一些

select A.act_id as '活動(dòng)編號(hào)', sum(O.order_pay) as '總金額', count(O.user_id) as '訂單數(shù)'
from orders as O
left join act_apply as A
on A.user_id = O.user_id
where A.act_time  <= O.order_time
group by A.act_id;

第二問(wèn)

先選出每個(gè)活動(dòng)開(kāi)始的時(shí)間

select A.act_id, min(A.act_time) as 'act_start' 
from act_apply as A
group by A.act_id;

第一問(wèn)可以得到總訂單數(shù) 兩表連接

select tmp1.act_id as  '活動(dòng)編號(hào)', tmp1.order_num/datediff(now(), tmp2.'act_start' ) as '活動(dòng)開(kāi)始后平均每天下單數(shù)'
from (select A.act_id , count(O.user_id) as 'order_num'
from orders as O
left join act_apply as A
on A.user_id = O.user_id
where A.act_time  <= O.order_time
group by A.act_id) as tmp1
join (select A.act_id, min(A.act_time) as 'act_start' 
from act_apply as A
group by A.act_id) as tmp2
on tmp1.act_id = tmp2.act_id

整體思路就是兩個(gè)子查詢(xún) 第一個(gè)先查出每個(gè)活動(dòng)的訂單數(shù)量第二個(gè)查出每個(gè)訂單的開(kāi)始日期兩表按照活動(dòng)id連接每個(gè)訂單的數(shù)量除以當(dāng)前日期與開(kāi)始日期的差值即可
(應(yīng)該是對(duì)的)

后邊發(fā)現(xiàn)自己的寫(xiě)法寫(xiě)復(fù)雜了其實(shí)第二個(gè)子查詢(xún)是沒(méi)必要的

select A.act_id as  '活動(dòng)編號(hào)', count(O.user_id)/datediff(now(), min(A.act_time))  as '活動(dòng)開(kāi)始后平均每天下單數(shù)' 
from orders as O
left join act_apply as A
on A.user_id = O.user_id
where A.act_time  <= O.order_time
group by A.act_id

作者的答案其實(shí)也寫(xiě)復(fù)雜了但是可以學(xué)習(xí)一下窗口函數(shù)

SELECT act_id as '活動(dòng)編號(hào)', COUNT(*)/DATEDIFF(NOW(),act_start) AS '活動(dòng)開(kāi)始后平均每天下單數(shù)'
FROM orders a
LEFT JOIN
(SELECT user_id ,act_id ,act_time, min(act_time) over(PARTITION by act_id) as 'act_start'
FROM act_apply
) b
ON a.user_id=b.user_id
WHERE order_time>=act_time
GROUP BY act_id,act_start

這里用到了一個(gè)over函數(shù) 具體用法在后邊進(jìn)行了補(bǔ)充
這個(gè)的思路與第一問(wèn)的思路一致只不過(guò)對(duì)于活動(dòng)表給出了每個(gè)活動(dòng)的開(kāi)始日期(使用over比較方便否則需要group by min 然后再做一次連接) 然后兩表連接分組計(jì)數(shù)除以時(shí)間差即可
需要注意的是我也覺(jué)得最后一行GROUP BY act_id,act_start 可以改為 GROUP BY act_id 但是作者給出了解釋:

如果group by 沒(méi)有act_start的話(huà) sql不允許select語(yǔ)句使用它的～是聚合函數(shù)的限制

補(bǔ)充
https://kknews.cc/code/66jnqzv.html
OVER的定義
OVER用于為行定義一個(gè)窗口，它對(duì)一組值進(jìn)行操作贵涵，不需要使用GROUP BY子句對(duì)數(shù)據(jù)進(jìn)行分組列肢，能夠在同一行中同時(shí)返回基礎(chǔ)行的列和聚合列。

OVER的語(yǔ)法

OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] )

PARTITION BY 子句進(jìn)行分組宾茂；
ORDER BY 子句進(jìn)行排序瓷马。
窗口函數(shù)OVER()指定一組行，開(kāi)窗函數(shù)計(jì)算從窗口函數(shù)輸出的結(jié)果集中各行的值跨晴。
開(kāi)窗函數(shù)不需要使用GROUP BY就可以對(duì)數(shù)據(jù)進(jìn)行分組欧聘，還可以同時(shí)返回基礎(chǔ)行的列和聚合列。

OVER的用法
OVER開(kāi)窗函數(shù)必須與聚合函數(shù)或排序函數(shù)一起使用端盆，聚合函數(shù)一般指SUM(),MAX(),MIN,COUNT(),AVG()等常見(jiàn)函數(shù)怀骤。排序函數(shù)一般指RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE()等。

具體例子可以參考以上鏈接

問(wèn)題二：

（考點(diǎn)：用戶(hù)行為路徑分析）

表1——用戶(hù)行為表tracking_log焕妙，大概字段有（user_id‘用戶(hù)編號(hào)’,opr_id‘操作編號(hào)’,log_time‘操作時(shí)間’）

要求：
1.計(jì)算每天的訪客數(shù)和他們的平均操作次數(shù)蒋伦。
2.統(tǒng)計(jì)每天符合以下條件的用戶(hù)數(shù)：A操作之后是B操作，AB操作必須相鄰焚鹊。

解答
問(wèn)題1：
先選出每天的訪客數(shù)和每天的操作次數(shù)

select T.log_time, count(distinct T.user_id) as '訪客數(shù)', count(T.opr_id) as '總操作次數(shù)'
from tracking_log as T
group by T.log_time

子查詢(xún)

select tmp.log_time, tmp.'訪客數(shù)', tmp.'總操作次數(shù)'/tmp.'訪客數(shù)' as '平均操作次數(shù)'
from (select T.log_time, count(distinct T.user_id) as '訪客數(shù)', count(T.opr_id) as '總操作次數(shù)'
from tracking_log as T
group by T.log_time) as tmp;

好像又寫(xiě)復(fù)雜了痕届。。不用子查詢(xún)就可以完成

select T.log_time, count(distinct T.user_id) as '訪客數(shù)', count(T.opr_id)/count(distinct T.user_id) as '平均操作次數(shù)'
from tracking_log as T
group by T.log_time

參考答案

SELECT b.date ,  COUNT(b.user_id) as '訪客數(shù)' , AVG(op) AS '平均操作次數(shù)'
FROM
(SELECT user_id , COUNT(opr_type) as 'op', CONVERT(log_time,date) as 'date' FROM tracking_log
GROUP BY user_id,date) b
GROUP BY b.date

思路：用group嵌套寺旺，剛好可以把人數(shù)和人次分解開(kāi)
子查詢(xún)對(duì)日期和user_id進(jìn)行分組得到每天每個(gè)人的操作次數(shù)
然后對(duì)日期進(jìn)行分組統(tǒng)計(jì)user_id的數(shù)量即為每天的訪客數(shù) 對(duì)每天每個(gè)人的操作次數(shù)取平均即為平均操作次數(shù)

需要注意的點(diǎn):
MySQL 的CAST()和CONVERT()函數(shù)可用來(lái)獲取一個(gè)類(lèi)型的值爷抓，并產(chǎn)生另一個(gè)類(lèi)型的值。兩者具體的語(yǔ)法如下：

CAST(value as type);
CONVERT(value, type);

這里的log_time需要用 CONVERT(log_time,date) as 'date' 處理

而且mysql的一個(gè)特點(diǎn)是group by 中的字段是可以使用 select中的別名的

MySQL extends standard SQL to permit noncolumn expressions in GROUP BY clauses and considers the preceding statement valid.

修改自己的答案

select CONVERT(T.log_time,date) , count(distinct T.user_id) as '訪客數(shù)', count(T.opr_id)/count(distinct T.user_id) as '平均操作次數(shù)'
from tracking_log as T
group by CONVERT(T.log_time,date)

問(wèn)題2:
刷過(guò)leetcode之后這個(gè)題還是比較簡(jiǎn)單的就是寫(xiě)起來(lái)有些復(fù)雜
先對(duì)時(shí)間排序?qū)τ诓煌膗ser_id創(chuàng)建按時(shí)間排序的rank

select T.user_id阻塑， CONVERT(T.log_time,date) as date蓝撇， T. opr_id,
@rank:= if(T.user_id = @pre_id,  @rank+1, 1)，
@pre_id:= T.user_id 
from tracking_log as T, (select @rank:=0, @pre_id:=NULL) as init
-- Mysql,order by 后跟 別名 表達(dá)式 均可
order by T.user_id, CONVERT(T.log_time,date) asc;

然后兩個(gè)上表進(jìn)行連接連接條件是user_id相等 tmp1.rank = tmp2.rank-1 (其實(shí)就是對(duì)于同一個(gè)user_id通過(guò)rank后后一天進(jìn)行連接)
然后篩選tmp1.opr_id = 'A' tmp2.opr_id = 'B'
最后對(duì)日期分組計(jì)數(shù)即可可能一天中用戶(hù)先A后B多次但只需要統(tǒng)計(jì)1次因此需要去重

select tmp1.date, count(distinct tmp1.user_id) as 'A-B路徑用戶(hù)計(jì)數(shù)' 
from (select T.user_id陈莽， CONVERT(T.log_time,date) as date渤昌， T. opr_id,
@rank:= if(T.user_id = @pre_id,  @rank+1, 1) as rank，
@pre_id:= T.user_id 
from tracking_log as T, (select @rank:=0, @pre_id:=NULL) as init
order by T.user_id, CONVERT(T.log_time,date) asc) as tmp1
join (select T.user_id走搁， CONVERT(T.log_time,date) as date独柑， T. opr_id,
@rank:= if(T.user_id = @pre_id,  @rank+1, 1) as rank，
@pre_id:= T.user_id 
from tracking_log as T, (select @rank:=0, @pre_id:=NULL) as init
order by T.user_id, CONVERT(T.log_time,date) asc)  as tmp2
on tmp1.rank = tmp2.rank - 1 and tmp1.user_id = tmp2.user_id
where tmp1.opr_id = 'A'  and tmp2.opr_id = 'B'
group by tmp1.date;

參考答案

ELECT a.Date , COUNT(*) as 'A-B路徑用戶(hù)計(jì)數(shù)'
FROM(
SELECT DISTINCT user_id as 'User',opr_type as '1st', CONVERT(log_time,date) as 'Date', lead(opr_type,1) 
over(PARTITION by user_id,CONVERT(log_time,date) ORDER BY log_time) as '2nd'
FROM tracking_log
) a
WHERE a.1st = 'A' and a.2nd ='B'
GROUP BY a.Date

確實(shí)利用窗口函數(shù)要簡(jiǎn)單很多
對(duì)user_id,和時(shí)間分組按時(shí)間排序取出后一個(gè)操作opr_id的寫(xiě)法是這樣的
lag(1) 后移一項(xiàng) lead(1)前移一項(xiàng) 這里應(yīng)該用 lead

select lead(T.opr_id) over( PARTITION by T.user_id,convert(T.log_time,date) order by T.log_time asc)
from tracking_log as T

整體選出是這樣的這里也需要去重

SELECT DISTINCT T.user_id as 'User', T.opr_type as '1st', CONVERT(T.log_time,date) as 'Date', lead(T.opr_type,1) 
over(PARTITION by T.user_id, CONVERT(T.log_time,date) ORDER BY CONVERT(T.log_time,date) ASC) as '2nd'
FROM tracking_log as T

最后剩下就是分組篩選然后計(jì)數(shù)了

問(wèn)題三

（考點(diǎn)：用戶(hù)新增留存分析）

表1——用戶(hù)登陸表user_log私植，大概字段有（user_id‘用戶(hù)編號(hào)’忌栅，log_time‘登陸時(shí)間’）

要求：

1.每天新增用戶(hù)數(shù)，以及他們第2天曲稼、30天的回訪比例

參考答案：

(找出每個(gè)用戶(hù)第一次登陸時(shí)間索绪，再聚合時(shí)間得到每一天新增用戶(hù)湖员，時(shí)間要聚合到天)

解答
先找出每個(gè)用戶(hù)的最小日期即為首次登陸的時(shí)間

select T.user_id, min(log_time) as first_time
from tracking_log as T
group by T.user_id

又要再篩選用一下窗口函數(shù)試一下吧

select T.user_id, min(log_time) over(PARTITION by convert(T.user_id, date)) as first_time
from tracking_log as T

然后對(duì)first_time進(jìn)行分組，統(tǒng)計(jì)去重的user_id個(gè)數(shù)即為當(dāng)天的新用戶(hù)數(shù)量

select tmp.first_time, count(distinct tmp.user_id) as new_count
from (select T.user_id, min(log_time) over(PARTITION by convert(T.user_id, date)) as first_time
from tracking_log as T) as tmp
group by tmp.first_time

第二天的回訪數(shù)量怎么求
登陸時(shí)間與首次時(shí)間的差值為1就可以進(jìn)行篩選

select tmp1.first_time, count(distinct tmp1.user_id) as 2nd_back
from (select T.user_id, min(log_time) over(PARTITION by convert(T.user_id, date)) as first_time, convert(T.user_id, date) as date
from tracking_log as T) as tmp1
where datediff(tmp1.date, tmp1.first_time) = 1
group by tmp1.first_time

同理一個(gè)月后的回訪

select tmp2.first_time, count(distinct tmp2.user_id) as 30nd_back
from (select T.user_id, min(log_time) over(PARTITION by convert(T.user_id, date)) as first_time, convert(T.user_id, date) as date
from tracking_log as T) as tmp2
where datediff(tmp2.date, tmp2.first_time) = 29
group by tmp2.first_time

以上三表進(jìn)行連接左連接

select a.first_time as '日期', a.new_count as '新增用戶(hù)',
concat(round(100*b.2nd_back/a.new_count, 2), '%') as '第2天回訪率'
concat(round(100*b.30nd_back/a.new_count, 2), '%') as '第30天回訪率' 
from (select tmp.first_time, count(distinct tmp.user_id) as new_count
from (select T.user_id, min(log_time) over(PARTITION by convert(T.user_id, date)) as first_time
from tracking_log as T) as tmp
group by tmp.first_time) as a
left join (select tmp1.first_time, count(distinct tmp1.user_id) as 2nd_back
from (select T.user_id, min(log_time) over(PARTITION by convert(T.user_id, date)) as first_time, convert(T.user_id, date) as date
from tracking_log as T) as tmp1
where datediff(tmp1.date, tmp1.first_time) = 1
group by tmp1.first_time) as b
on a.first_time = b.first_time
left join (select tmp2.first_time, count(distinct tmp2.user_id) as 30nd_back
from (select T.user_id, min(log_time) over(PARTITION by convert(T.user_id, date)) as first_time, convert(T.user_id, date) as date
from tracking_log as T) as tmp2
where datediff(tmp2.date, tmp2.first_time) = 29
group by tmp2.first_time) as c
on a.first_time = c.first_time

第二部分：計(jì)算題1道

問(wèn)題四：

（考點(diǎn)：貝葉斯公式的應(yīng)用）

已知A,B廠生產(chǎn)的產(chǎn)品的次品率分別是1%和2%瑞驱，現(xiàn)在由A,B產(chǎn)品分別占60%娘摔、40%的樣品中隨機(jī)抽一件，若取到的是次品唤反，求此次品是B廠生產(chǎn)的概率凳寺。

第三部分：綜合分析題1道

問(wèn)題五：

（考點(diǎn)：產(chǎn)品更新決策的數(shù)據(jù)支持）

某網(wǎng)站優(yōu)化了商品詳情頁(yè)，現(xiàn)在新舊兩個(gè)版本同時(shí)運(yùn)行彤侍，新版頁(yè)面覆蓋了10%的用戶(hù)肠缨，舊版覆蓋90%的用戶(hù)。現(xiàn)在需要了解盏阶，新版頁(yè)面是否能夠提高商品詳情頁(yè)到支付頁(yè)的轉(zhuǎn)化率怜瞒，并決定是否要覆蓋舊版，你能為決策提供哪些信息般哼，需要收集哪些指標(biāo)，給出統(tǒng)計(jì)方法及過(guò)程惠窄。

這個(gè)是A/B test 的問(wèn)題先學(xué)習(xí)一下再返回來(lái)看這個(gè)問(wèn)題

最后編輯于：2020.07.29 18:10:51

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末蒸眠，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子杆融，更是在濱河造成了極大的恐慌楞卡，老刑警劉巖，帶你破解...
沈念sama閱讀 212,454評(píng)論 6贊 493
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件脾歇，死亡現(xiàn)場(chǎng)離奇詭異蒋腮，居然都是意外死亡，警方通過(guò)查閱死者的電腦和手機(jī)藕各，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 90,553評(píng)論 3贊 385
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門(mén)池摧，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)，“玉大人激况，你說(shuō)我怎么就攤上這事作彤。” “怎么了乌逐？”我有些...
開(kāi)封第一講書(shū)人閱讀 157,921評(píng)論 0贊 348
道士緝兇錄：失蹤的賣(mài)姜人
文/不壞的土叔我叫張陵竭讳，是天一觀的道長(zhǎng)。經(jīng)常有香客問(wèn)我浙踢，道長(zhǎng)绢慢，這世上最難降的妖魔是什么？我笑而不...
開(kāi)封第一講書(shū)人閱讀 56,648評(píng)論 1贊 284
?港島之戀（遺憾婚禮）
正文為了忘掉前任洛波，我火速辦了婚禮胰舆，結(jié)果婚禮上骚露，老公的妹妹穿的比我還像新娘。我一直安慰自己思瘟，他們只是感情好荸百，可當(dāng)我...
茶點(diǎn)故事閱讀 65,770評(píng)論 6贊 386
惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
文/花漫我一把揭開(kāi)白布。她就那樣靜靜地躺著滨攻，像睡著了一般够话。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上光绕，一...
開(kāi)封第一講書(shū)人閱讀 49,950評(píng)論 1贊 291
城市分裂傳說(shuō)
那天女嘲，我揣著相機(jī)與錄音，去河邊找鬼诞帐。笑死欣尼，一個(gè)胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的停蕉。我是一名探鬼主播愕鼓，決...
沈念sama閱讀 39,090評(píng)論 3贊 410
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開(kāi)眼，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼慧起！你這毒婦竟也來(lái)了菇晃？” 一聲冷哼從身側(cè)響起，我...
開(kāi)封第一講書(shū)人閱讀 37,817評(píng)論 0贊 268
萬(wàn)榮殺人案實(shí)錄
序言：老撾萬(wàn)榮一對(duì)情侶失蹤蚓挤，失蹤者是張志新（化名）和其女友劉穎磺送，沒(méi)想到半個(gè)月后，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體灿意，經(jīng)...
沈念sama閱讀 44,275評(píng)論 1贊 303
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡估灿，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 36,592評(píng)論 2贊 327
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了缤剧。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片馅袁。...
茶點(diǎn)故事閱讀 38,724評(píng)論 1贊 341
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖鞭执，靈堂內(nèi)的尸體忽然破棺而出司顿，到底是詐尸還是另有隱情，我是刑警寧澤兄纺，帶...
沈念sama閱讀 34,409評(píng)論 4贊 333
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布大溜，位于F島的核電站，受9級(jí)特大地震影響估脆，放射性物質(zhì)發(fā)生泄漏钦奋。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 40,052評(píng)論 3贊 316
男人毒藥：我在死后第九天來(lái)索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望付材。院中可真熱鬧朦拖，春花似錦、人聲如沸厌衔。這莊子的主人今日做“春日...
開(kāi)封第一講書(shū)人閱讀 30,815評(píng)論 0贊 21
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽(yáng)富寿。三九已至睬隶，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間页徐，已是汗流浹背苏潜。一陣腳步聲響...
開(kāi)封第一講書(shū)人閱讀 32,043評(píng)論 1贊 266
情欲美人皮
我被黑心中介騙來(lái)泰國(guó)打工，沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留变勇，地道東北人恤左。一個(gè)月前我還...
沈念sama閱讀 46,503評(píng)論 2贊 361
代替公主和親
正文我出身青樓，卻偏偏與公主長(zhǎng)得像搀绣，于是被迫代替她去往敵國(guó)和親飞袋。傳聞我的和親對(duì)象是個(gè)殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 43,627評(píng)論 2贊 350