更多數(shù)據(jù)分析思維，工具和實(shí)際項(xiàng)目干貨文章，請移步共粽號：【數(shù)據(jù)分析星球】，還有數(shù)分資料包領(lǐng)取！
01 寫在前面
數(shù)據(jù)分析SQL筆試題系列第3篇來啦！之前筆試題的文章如果沒有看可以戳：
無論你是剛畢業(yè)的職場小鮮肉、還是想轉(zhuǎn)行數(shù)據(jù)分析的小白玩家，只要想進(jìn)入數(shù)據(jù)分析的行業(yè)，都逃不過數(shù)據(jù)分析面試的考驗(yàn)，這里最重要也是最關(guān)鍵的一關(guān)就是SQL筆試了，不過不用擔(dān)心，結(jié)合作者6年+的工作和面試經(jīng)驗(yàn)，系統(tǒng)全面地整理了數(shù)據(jù)分析面試中那些高頻出現(xiàn)的以及各大廠的SQL筆試題，學(xué)習(xí)了這些筆試題的常見套路和解法，把這些題目都刷一遍，在接下來的筆試中應(yīng)該可以一往無前，收割offer啦！
SQL筆試不僅考查代碼的熟練程度，更多的考查的是對業(yè)務(wù)的理解程度，只有理解了業(yè)務(wù)背景和邏輯，才能更快速準(zhǔn)確地給出答案。本篇選取了PDD的業(yè)務(wù)筆試題，在某個業(yè)務(wù)背景下考查SQL能力，趕緊來看看這些題目你都會么？
02 用戶行為分析
業(yè)務(wù)背景
某購物APP最近上線了一個新功能，用戶簽到后可以跳轉(zhuǎn)到大轉(zhuǎn)盤抽獎，抽獎獲得的獎金可以抵消購物的費(fèi)用，以此來培養(yǎng)用戶使用app的習(xí)慣。
數(shù)據(jù)表介紹
現(xiàn)有一張用戶行為表user_log，主要字段如下，記錄了用戶在app上的所有行為日志，即何人user_id在何時(shí)event_time進(jìn)行了什么操作event_id。
圖片
需求：
1、計(jì)算該app每天的訪客數(shù)以及每天人均行為次數(shù)。
2、統(tǒng)計(jì)每天簽到之后并進(jìn)行抽獎的用戶數(shù)，注意簽到和抽獎行為必須相鄰（簽到和抽獎行為對應(yīng)的event_id分別為'register','gift'）。
思路：
第1問比較簡單，計(jì)算app每天的訪客數(shù)，因?yàn)橛脩舻卿浽L問app就會在表中產(chǎn)生對應(yīng)的行為日志，所以每天的訪客數(shù)只需要按天對用戶數(shù)去重即可，每天人均行為次數(shù)的計(jì)算，因?yàn)橐淮涡袨榫蜁a(chǎn)生一條記錄，所以，人均行為次數(shù)就是所有的記錄計(jì)數(shù)，除以總的訪客數(shù)。
代碼如下：
select cast(event_time as date) as day,
count(distinct user_id) as active_cnt,
count( event_id)/count(distinct user_id) as avg_opr_cnt
from user_log
group by cast(event_time as date);
第2問升級了難度，雖然也是統(tǒng)計(jì)用戶數(shù)，但是添加了限制：簽到之后要大轉(zhuǎn)盤抽獎，兩個行為一前一后必須相鄰才可以。這個時(shí)候我們可以用窗口函數(shù)的位移函數(shù)lead（）over（）實(shí)現(xiàn)，lead可以取當(dāng)前記錄的下一條記錄，如果我們對每個用戶user_id分組，按照行為時(shí)間event_time升序排列，就可以得到一個用戶的連續(xù)的行為記錄，再用lead（）就可以得到下一條記錄，從而在當(dāng)前記錄中得到下一條記錄，對兩個連續(xù)行為進(jìn)行篩選，就可以計(jì)算滿足這個條件的用戶數(shù)。
代碼如下：
select a.day,count(distinct user_id)
from(
? ? select user_id,
? ? cast(event_time as date) as day,
? ? event_id,
? ? lead(event_id,1) over(partition by user_id order by event_time ) as next_event_id
? ? from user_log
)a
where event_id='register' and next_event_id='gift'
group by a.day;
03 活動效果數(shù)據(jù)分析
業(yè)務(wù)背景
為了提高某店鋪的營業(yè)額，最近運(yùn)營部門上線了多個運(yùn)營活動，用戶參與活動后可以領(lǐng)取不定額的優(yōu)惠券，以此鼓勵用戶下單。但每個用戶限參與一個活動，不可同時(shí)參與多個活動。
數(shù)據(jù)表介紹
現(xiàn)有一張訂單表orders和活動報(bào)名表act_join,分別記錄了用戶的下單明細(xì)和用戶報(bào)名的活動明細(xì)。具體字段如下：
訂單表orders，大概字段有（user_id‘用戶編號’,order_id? '訂單編號' ，order_sales‘訂單金額’ , order_time‘下單時(shí)間’）。
活動報(bào)名表act_join，大概字段有（act_id‘活動編號’, user_id‘報(bào)名用戶’,join_time‘報(bào)名時(shí)間’）
需求：
1. 統(tǒng)計(jì)每個活動報(bào)名的所有用戶在報(bào)名后產(chǎn)生的總訂單金額，總訂單數(shù)。（每個用戶限報(bào)一個活動,且默認(rèn)用戶報(bào)名后產(chǎn)生的訂單均為參加活動的訂單）。
2. 統(tǒng)計(jì)每個活動從開始后到當(dāng)天（數(shù)據(jù)統(tǒng)計(jì)日）平均每天產(chǎn)生的訂單數(shù)，活動開始時(shí)間定義為最早有用戶報(bào)名的時(shí)間。
思路：
第1問計(jì)算總訂單金額和總訂單數(shù)，這兩個指標(biāo)都比較簡單sum(order_sales)、count(order_id)就可以，但是關(guān)鍵在于限定條件，是每個活動報(bào)名后的用戶的匯總，必須是報(bào)名了某個活動，且必須在活動開始后的數(shù)據(jù)統(tǒng)計(jì)?？梢酝ㄟ^訂單表orders和報(bào)名表act_join連接，限定訂單時(shí)間大于等于活動的開始時(shí)間即可。
代碼如下：
select? t2.act_id,
count(t1.order_time) as order_cnt,
sum(order_sales) as order_sales_sum
from
(
? ? select user_id,order_id,order_sales,order_time
? ? from orders
)t1
inner join (
? ? select user_id,act_id,join_time
? ? from act_join
)t2
on t1.user_id=t2.user_id
where t1.order_time>=t2.join_time
group by t2.act_id;
第2問與第1問有相似之處，同樣是用戶報(bào)名后的下單，只是多了一些限定條件：同時(shí)要滿足要小于等于計(jì)算日期當(dāng)天，也就是程序運(yùn)行的系統(tǒng)時(shí)間now()，在此基礎(chǔ)上，計(jì)算整體的訂單數(shù)，除以活動進(jìn)行的天數(shù)，就是該活動每天的平均下單數(shù)。
代碼如下：
select t1.act_id,
count(order_id)/datediff(now(),min(t1.begin_time))--總店單數(shù)/活動天數(shù)
from
(
? ? select act_id,
? ? user_id,
? ? join_time,
? ? min(join_time) over(partition by act_id) as begin_time? --當(dāng)前活動的開始時(shí)間
? ? from act_join
)t1
inner join
(
? ? select user_id,
? ? order_id,
? ? order_time
? ? from orders
)t2
on t1.user_id=t2.user_id
where t1.join_time between t1.begin_time and now() --活動開始至今的數(shù)據(jù)
and t2.order_time >= t1.join_time --活動開始后的下單
group by t1.act_id;
說明：這里使用了窗口函數(shù)，計(jì)算了每個活動的開始時(shí)間，然后join 訂單表，通過where條件將上面的限定條件滿足。當(dāng)然這只是一種解題思路，還有很多其他的解法，大家可以嘗試。
上面通過2個實(shí)際的業(yè)務(wù)場景，不僅考查了SQL的代碼能力，更主要的是考查了大家對業(yè)務(wù)場景的理解能力，如果理解不了業(yè)務(wù)場景，也就很難寫出SQL代碼，相反，如果業(yè)務(wù)場景非常熟悉，代碼就比較簡單了，所以，我們在練習(xí)SQL的時(shí)候，一定要結(jié)合著業(yè)務(wù)場景來練，這樣才比較貼合實(shí)際業(yè)務(wù)場景，也更能在筆試中有余，脫穎而出啦！
以上就是數(shù)分面試寶典系列—SQL高頻筆試題第4篇文章的內(nèi)容，部分歷史文章請回翻公眾號，更多數(shù)據(jù)分析面試筆試的文章持續(xù)更新中，敬請期待，如果覺得不錯，也歡迎分享、點(diǎn)贊和收藏哈~
