close
SQL幾乎是每個數據崗的必備題目，下面分享幾個常見的大廠SQL習題。
（1）找出連續7天登陸，連續30天登陸的用戶（小紅書筆試，電信雲面試），最大連續登陸天數的問題 --窗口函數
（2）求連續點擊三次的用戶數，中間不能有別人的點擊 ，最大連續天數的變形問題（騰訊微保面試）--窗口函數
（3）計算除去部門最高工資，和最低工資的平均工資（字節跳動面試）--窗口函數
（4）留存的計算，和累計求和的計算 --窗口函數，自聯結（pdd面試）
（5）AB球隊得分流水錶，得到連續三次得分的隊員名字 和每次趕超對手的球員名字,（pdd面試）
把這幾類題型吃透，再也不怕手撕SQL和筆試了，其中最難的是題（5）,整個面試的sql基本上都是窗口函數的玩法，搭配case when 也考得比較多。
(1) 找出連續7天登陸，連續30天登陸的用戶
select*from（selectuser_id,count(1)asnumfrom(selectuser_id,date_sub(log_in_date,rank)dtsfrom(selectuser_id,log_in_date,row_number()over(partitionedbyuser_idorderbylog_in_date)asrankfromuser_log)t)agroupbydts）bwherenum=7(2)求連續點擊三次的用戶數，而且中間不能有別人的點擊,
a表記錄了點擊的流水信息，包括用戶id ，和點擊時間
usr_idaabaaaaclick_timet1t2t3t4t5t6t7row_number() over(order by click_time) as rank_1 得到rank_1為 1 2 3 4 5 6 7
row_number() over(partition by usr_id order by click_time) 得到rank_2 為 1 2 1 3 4 5 6
rank_1- rank2 得到diff 為 0 0 2 1 1 1 1
這時我們發現只需要對diff進行分組計數大於3個，就是連續點擊大於三且中間沒有其他人點擊的用戶
selectdistinctusr_idfrom(select*,rank_1-rank2asdifffrom(select*,row_number()over(orderbyclick_time)asrank_1row_number()over(partitionbyusr_idorderbyclick_time)asrank_2froma)b)cgroupbydiff,usr_idhavingcount(diff)>=3(3)計算除去部門最高工資，和最低工資的平均工資（字節跳動面試）--窗口函數
emp 表
id 員工 id ，deptno 部門編號，salary 工資
核心是使用窗口函數降序和升序分別排一遍就取出了最高和最低。
selecta.deptno，avg(a.salary)from(select*,rank()over(partitionbydeptnoorderbysalary)asrank_1,rank()over(partitionbydeptnoorderbysalarydesc)asrank_2fromemp)agroupbya.deptnowherea.rank_1>1anda.rank_2>1(4) 留存的計算，和累計求和的計算 --窗口函數，自聯結（pdd面試）
手機中的相機是深受大家喜愛的應用之一，下圖是某手機廠商數據庫中的用戶行為信息表中部分數據的截圖
現在該手機廠商想要分析手機中的應用（相機）的活躍情況，需統計如下數據：
需要獲得的數據的格式如下：
selectd.a_t,count(distinctcasewhend.時間間隔=1thend.用戶idelsenullend)as次日留存數,count(distinctcasewhen時間間隔=1thend.用戶idelsenullend)/count(distinctd.用戶id)as次日留存率,count(distinctcasewhend.時間間隔=3thend.用戶idelsenullend)as3日留存數,count(distinctcasewhen時間間隔=3thend.用戶idelsenullend)/count(distinctd.用戶id)as3日留存率,count(distinctcasewhend.時間間隔=7thend.用戶idelsenullend)as7日留存數,count(distinctcasewhen時間間隔=7thend.用戶idelsenullend)/count(distinctd.用戶id)as7日留存率from(select*,timestampdiff(day,a_t,b_t)as時間間隔from(selecta.`用戶id`,a.登陸時間asa_t,b.登陸時間asb_tfrom登錄信息asaleftjoin登錄信息asbona.`用戶id`=b.`用戶id`wherea.應用名稱='相機'ANDb.應用名稱='相機')asc)asdgroupbyd.a_t;(5)AB球隊得分流水錶，得到連續三次得分的隊員名字 和每次趕超對手的球員名字（pdd）
在復盤時發現有類似原題，這是我在面試中遇到的最難的題
問題：兩支籃球隊進行了激烈的籃球比賽，比分交替上升。比賽結束後，你有一張兩隊得分分數的明細表，記錄了球隊team，球員號碼number，球員姓名name, 得分分數score 以及得分時間scoretime(datetime)。現在球隊要對比賽中表現突出的球員做出嘉獎，所以請你用sql統計出
1)連續三次（及以上）為球隊得分的球員名單
2)比賽中幫助各自球隊反超比分的球員姓名以及對應時間。
先建一個類似的表
CREATETABLEbasketball_game_score_detail(teamVARCHAR(40)NOTNULL,numberVARCHAR(100)NOTNULL,score_timedatetimeNOTNULL,scoreintNOTNULL,namevarchar(100)NOTNULL);insertintobasketball_game_score_detailvalues('A',1,'2020/8/289:01:14',1,'A1');insertintobasketball_game_score_detailvalues('A',5,'2020/8/289:02:28',1,'A5');insertintobasketball_game_score_detailvalues('B',4,'2020/8/289:03:42',3,'B4');insertintobasketball_game_score_detailvalues('A',4,'2020/8/289:04:55',3,'A4');insertintobasketball_game_score_detailvalues('B',1,'2020/8/289:06:09',3,'B1');insertintobasketball_game_score_detailvalues('A',3,'2020/8/289:07:23',3,'A3');insertintobasketball_game_score_detailvalues('A',4,'2020/8/289:08:37',3,'A4');insertintobasketball_game_score_detailvalues('B',1,'2020/8/289:09:51',2,'B1');insertintobasketball_game_score_detailvalues('B',2,'2020/8/289:11:05',2,'B2');insertintobasketball_game_score_detailvalues('B',4,'2020/8/289:12:18',1,'B4');insertintobasketball_game_score_detailvalues('A',1,'2020/8/289:13:32',2,'A1');insertintobasketball_game_score_detailvalues('A',1,'2020/8/289:14:46',1,'A1');insertintobasketball_game_score_detailvalues('A',4,'2020/8/289:16:00',1,'A4');insertintobasketball_game_score_detailvalues('B',3,'2020/8/289:17:14',3,'B3');insertintobasketball_game_score_detailvalues('B',2,'2020/8/289:18:28',3,'B2');insertintobasketball_game_score_detailvalues('A',2,'2020/8/289:19:42',3,'A2');insertintobasketball_game_score_detailvalues('A',1,'2020/8/289:20:55',1,'A1');insertintobasketball_game_score_detailvalues('B',3,'2020/8/289:22:09',2,'B3');insertintobasketball_game_score_detailvalues('B',3,'2020/8/289:23:23',3,'B3');insertintobasketball_game_score_detailvalues('A',5,'2020/8/289:24:37',2,'A5');insertintobasketball_game_score_detailvalues('B',1,'2020/8/289:25:51',3,'B1');insertintobasketball_game_score_detailvalues('B',2,'2020/8/289:27:05',1,'B2');insertintobasketball_game_score_detailvalues('A',3,'2020/8/289:28:18',1,'A3');insertintobasketball_game_score_detailvalues('B',4,'2020/8/289:29:32',1,'B4');insertintobasketball_game_score_detailvalues('A',1,'2020/8/289:30:46',3,'A1');insertintobasketball_game_score_detailvalues('B',1,'2020/8/289:32:00',1,'B1');insertintobasketball_game_score_detailvalues('A',4,'2020/8/289:33:14',2,'A4');insertintobasketball_game_score_detailvalues('B',1,'2020/8/289:34:28',1,'B1');insertintobasketball_game_score_detailvalues('B',5,'2020/8/289:35:42',2,'B5');insertintobasketball_game_score_detailvalues('A',1,'2020/8/289:36:55',1,'A1');insertintobasketball_game_score_detailvalues('B',1,'2020/8/289:38:09',3,'B1');insertintobasketball_game_score_detailvalues('A',1,'2020/8/289:39:23',3,'A1');insertintobasketball_game_score_detailvalues('B',2,'2020/8/289:40:37',3,'B2');insertintobasketball_game_score_detailvalues('A',3,'2020/8/289:41:51',3,'A3');insertintobasketball_game_score_detailvalues('A',1,'2020/8/289:43:05',2,'A1');insertintobasketball_game_score_detailvalues('B',3,'2020/8/289:44:18',3,'B3');insertintobasketball_game_score_detailvalues('A',5,'2020/8/289:45:32',2,'A5');insertintobasketball_game_score_detailvalues('B',5,'2020/8/289:46:46',3,'B5');
這裡我使用了lead和lag來取每個組的前幾個值，這個和最大聯繫天數不太一樣，但也可以用類似思路去解，但是使用lead和lag做起來更容易理解
selectdistincta.name,a.teamfrom(select*,lead(name,1)over(partitionbyteamorderbyscore_time)asld1,lead(name,2)over(partitionbyteamorderbyscore_time)asld2,lag(name,1)over(partitionbyteamorderbyscore_time)aslg1,lag(name,2)over(partitionbyteamorderbyscore_time)aslg2fromtable)awhere(a.name=a.ld1anda.name=a.ld2)or(a.name=a.ld1anda.name=a.lg1)or(a.name=a.lg1anda.name=a.lg2)第二小問面試時沒完全做出來，說了下思路，現在想了想當時的思路還是有問題，而且這個題也並不難，核心還是記錄每個時刻的累計得分表
SELECTTEAM,number,name,score_time,score,casewhenteam='A'thenscoreelse0endasA_score,casewhenteam='B'thenscoreelse0endB_scoreFROMbasketball_game_score_detailORDERBYSCORE_time
如下得到每個時刻的累計得分表
selectteam,number,name,score_time,A_score,b_score,sum(A_score)over(orderbyscore_time)asa_sum_score2,sum(b_score)over(orderbyscore_time)asb_sum_score2from(SELECTTEAM,number,name,score_time,score,casewhenteam='A'thenscoreelse0endasA_score,casewhenteam='B'thenscoreelse0endB_scoreFROMbasketball_game_score_detailORDERBYSCORE_time)asx
計算每個時刻的累計得分差，和上個時間的累計得分差，只要兩個的符號相反就是反超時刻。感覺思路還是比較簡潔的。
select*,score_gap*last_score_gapfrom(select*,a_sum_score2-b_sum_score2asscore_gap,lag(a_sum_score2-b_sum_score2,1)over(orderbyscore_time)aslast_score_gapfrom(selectteam,number,name,score_time,A_score,b_score,sum(A_score)over(orderbyscore_time)asa_sum_score2,sum(b_score)over(orderbyscore_time)asb_sum_score2from(SELECTTEAM,number,name,score_time,score,casewhenteam='A'thenscoreelse0endasA_score,casewhenteam='B'thenscoreelse0endB_scoreFROMbasketball_game_score_detailORDERBYSCORE_time)asx)asy)aszwherez.score_gap*last_score_gap<=0anda_sum_score2<>b_sum_score2當然，不光會有SQL課程提供，我們還會有大廠數據大佬幫你答疑，包括技術、求職、面試，趕緊來吧！
文章來源知乎：絢麗的小海螺
全站熱搜
留言列表