5個(gè)必考的大廠SQL面試題

SQL幾乎是每個(gè)數(shù)據(jù)崗的必備題目，下面分享幾個(gè)常見的大廠SQL習(xí)題。

（1）找出連續(xù)7天登陸，連續(xù)30天登陸的用戶（小紅書筆試，電信云面試），最大連續(xù)登陸天數(shù)的問題 --窗口函數(shù)

（2）求連續(xù)點(diǎn)擊三次的用戶數(shù)，中間不能有別人的點(diǎn)擊 ，最大連續(xù)天數(shù)的變形問題（騰訊微保面試）--窗口函數(shù)

（3）計(jì)算除去部門最高工資，和最低工資的平均工資（字節(jié)跳動(dòng)面試）--窗口函數(shù)

（4）留存的計(jì)算，和累計(jì)求和的計(jì)算 --窗口函數(shù)，自聯(lián)結(jié)（pdd面試）

（5）AB球隊(duì)得分流水表，得到連續(xù)三次得分的隊(duì)員名字 和每次趕超對(duì)手的球員名字,（pdd面試）

把這幾類題型吃透，再也不怕手撕SQL和筆試了，其中最難的是題（5）,整個(gè)面試的sql基本上都是窗口函數(shù)的玩法，搭配case when 也考得比較多。

(1) 找出連續(xù)7天登陸，連續(xù)30天登陸的用戶

select?*
from
（
??select?user_id?,count(1)?as?num
??from
?????(select?user_id,date_sub(log_in_date,?rank)?dts
??????????f?rom??(select?user_id,log_in_date,?
??????????????????row_number()?over(partitioned?by?user_id?order?by?log_in_date?)?as?rank
????from?user_log
???????????)t
??????)a
??group?by?dts
）b
where?num?=?7??

(2)求連續(xù)點(diǎn)擊三次的用戶數(shù)，而且中間不能有別人的點(diǎn)擊,

a表記錄了點(diǎn)擊的流水信息，包括用戶id ，和點(diǎn)擊時(shí)間

usr_id?a?a?b?a?a?a?a

click_time?t1?t2?t3?t4?t5?t6?t7

row_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

這時(shí)我們發(fā)現(xiàn)只需要對(duì)diff進(jìn)行分組計(jì)數(shù)大于3個(gè)，就是連續(xù)點(diǎn)擊大于三且中間沒有其他人點(diǎn)擊的用戶

select?distinct?usr_id
from????
(
???select?*,?rank_1-?rank2??as?diff
???from
??(
??????select?*,
??????row_number()?over(order?by?click_time)?as??rank_1
??????row_number()?over(partition?by?usr_id?order?by?click_time)?as?rank_2
??????from?a
???)?b
)?c
group?by?diff,usr_id
having?count(diff)?>=3

(3)計(jì)算除去部門最高工資，和最低工資的平均工資（字節(jié)跳動(dòng)面試）--窗口函數(shù)

emp 表

id 員工 id ，deptno 部門編號(hào)，salary 工資

核心是使用窗口函數(shù)降序和升序分別排一遍就取出了最高和最低。

select?a.deptno，avg(a.salary)
from??
?(
?select?*,?rank()?over(?partition?by?deptno?order?by?salary?)?as?rank_1
?,?rank()?over(?partition?by?deptno?order?by?salary?desc)?as?rank_2?
?from?emp
?)??a?
group?by?a.deptno
where?a.rank_1?>1?and?a.rank_2?>1?

(4) 留存的計(jì)算，和累計(jì)求和的計(jì)算 --窗口函數(shù)，自聯(lián)結(jié)（pdd面試）

手機(jī)中的相機(jī)是深受大家喜愛的應(yīng)用之一，下圖是某手機(jī)廠商數(shù)據(jù)庫中的用戶行為信息表中部分?jǐn)?shù)據(jù)的截圖

現(xiàn)在該手機(jī)廠商想要分析手機(jī)中的應(yīng)用（相機(jī)）的活躍情況，需統(tǒng)計(jì)如下數(shù)據(jù)：

需要獲得的數(shù)據(jù)的格式如下：

select?d.a_t,count(distinct?case?when?d.時(shí)間間隔=1?then?d.用戶id?????
???????????????else?null
???????????????end)?as??次日留存數(shù),?
count(distinct?case?when?時(shí)間間隔=1?then?d.用戶id
???????????????else?null
???????????????end)?/count(distinct?d.用戶id)?as?次日留存率,
count(distinct?case?when?d.時(shí)間間隔=3?then?d.用戶id?????
???????????????else?null
???????????????end)?as??3日留存數(shù)?,
count(distinct?case?when?時(shí)間間隔=3?then?d.用戶id
???????????????else?null
???????????????end)?/count(distinct?d.用戶id)?as?3日留存率,
count(distinct?case?when?d.時(shí)間間隔=7?then?d.用戶id?????
???????????????else?null
???????????????end)?as??7日留存數(shù)?,
count(distinct?case?when?時(shí)間間隔=7?then?d.用戶id
???????????????else?null
???????????????end)?/count(distinct?d.用戶id)?as?7日留存率

from
(select?*,timestampdiff(day,a_t,b_t)?as?時(shí)間間隔
from?(select?a.`用戶id`,a.登陸時(shí)間?as?a_t?,b.登陸時(shí)間?as?b_t
from?登錄信息?as?a??
left?join?登錄信息?as?b
on?a.`用戶id`=b.`用戶id`
where?a.應(yīng)用名稱=?'相機(jī)'?AND?b.應(yīng)用名稱='相機(jī)')?as?c)?as?d
group?by?d.a_t;?

(5)AB球隊(duì)得分流水表，得到連續(xù)三次得分的隊(duì)員名字 和每次趕超對(duì)手的球員名字（pdd）

在復(fù)盤時(shí)發(fā)現(xiàn)有類似原題，這是我在面試中遇到的最難的題

問題：兩支籃球隊(duì)進(jìn)行了激烈的籃球比賽，比分交替上升。比賽結(jié)束后，你有一張兩隊(duì)得分分?jǐn)?shù)的明細(xì)表，記錄了球隊(duì)team，球員號(hào)碼number，球員姓名name, 得分分?jǐn)?shù)score 以及得分時(shí)間scoretime(datetime)?，F(xiàn)在球隊(duì)要對(duì)比賽中表現(xiàn)突出的球員做出嘉獎(jiǎng)，所以請(qǐng)你用sql統(tǒng)計(jì)出

1)連續(xù)三次（及以上）為球隊(duì)得分的球員名單

2)比賽中幫助各自球隊(duì)反超比分的球員姓名以及對(duì)應(yīng)時(shí)間。

先建一個(gè)類似的表

CREATE?TABLE?basketball_game_score_detail(
???team??VARCHAR(40)?NOT?NULL?,
???number?VARCHAR(100)?NOT?NULL,
???score_time?datetime?NOT?NULL,
???score?int?NOT?NULL,
???name?varchar(100)??NOT?NULL
);
insert?into??basketball_game_score_detail?values('A',1,'2020/8/28?9:01:14',1,'A1');
insert?into??basketball_game_score_detail?values('A',5,'2020/8/28?9:02:28',1,'A5');
insert?into??basketball_game_score_detail?values('B',4,'2020/8/28?9:03:42',3,'B4');
insert?into??basketball_game_score_detail?values('A',4,'2020/8/28?9:04:55',3,'A4');
insert?into??basketball_game_score_detail?values('B',1,'2020/8/28?9:06:09',3,'B1');
insert?into??basketball_game_score_detail?values('A',3,'2020/8/28?9:07:23',3,'A3');
insert?into??basketball_game_score_detail?values('A',4,'2020/8/28?9:08:37',3,'A4');
insert?into??basketball_game_score_detail?values('B',1,'2020/8/28?9:09:51',2,'B1');
insert?into??basketball_game_score_detail?values('B',2,'2020/8/28?9:11:05',2,'B2');
insert?into??basketball_game_score_detail?values('B',4,'2020/8/28?9:12:18',1,'B4');
insert?into??basketball_game_score_detail?values('A',1,'2020/8/28?9:13:32',2,'A1');
insert?into??basketball_game_score_detail?values('A',1,'2020/8/28?9:14:46',1,'A1');
insert?into??basketball_game_score_detail?values('A',4,'2020/8/28?9:16:00',1,'A4');
insert?into??basketball_game_score_detail?values('B',3,'2020/8/28?9:17:14',3,'B3');
insert?into??basketball_game_score_detail?values('B',2,'2020/8/28?9:18:28',3,'B2');
insert?into??basketball_game_score_detail?values('A',2,'2020/8/28?9:19:42',3,'A2');
insert?into??basketball_game_score_detail?values('A',1,'2020/8/28?9:20:55',1,'A1');
insert?into??basketball_game_score_detail?values('B',3,'2020/8/28?9:22:09',2,'B3');
insert?into??basketball_game_score_detail?values('B',3,'2020/8/28?9:23:23',3,'B3');
insert?into??basketball_game_score_detail?values('A',5,'2020/8/28?9:24:37',2,'A5');
insert?into??basketball_game_score_detail?values('B',1,'2020/8/28?9:25:51',3,'B1');
insert?into??basketball_game_score_detail?values('B',2,'2020/8/28?9:27:05',1,'B2');
insert?into??basketball_game_score_detail?values('A',3,'2020/8/28?9:28:18',1,'A3');
insert?into??basketball_game_score_detail?values('B',4,'2020/8/28?9:29:32',1,'B4');
insert?into??basketball_game_score_detail?values('A',1,'2020/8/28?9:30:46',3,'A1');
insert?into??basketball_game_score_detail?values('B',1,'2020/8/28?9:32:00',1,'B1');
insert?into??basketball_game_score_detail?values('A',4,'2020/8/28?9:33:14',2,'A4');
insert?into??basketball_game_score_detail?values('B',1,'2020/8/28?9:34:28',1,'B1');
insert?into??basketball_game_score_detail?values('B',5,'2020/8/28?9:35:42',2,'B5');
insert?into??basketball_game_score_detail?values('A',1,'2020/8/28?9:36:55',1,'A1');
insert?into??basketball_game_score_detail?values('B',1,'2020/8/28?9:38:09',3,'B1');
insert?into??basketball_game_score_detail?values('A',1,'2020/8/28?9:39:23',3,'A1');
insert?into??basketball_game_score_detail?values('B',2,'2020/8/28?9:40:37',3,'B2');
insert?into??basketball_game_score_detail?values('A',3,'2020/8/28?9:41:51',3,'A3');
insert?into??basketball_game_score_detail?values('A',1,'2020/8/28?9:43:05',2,'A1');
insert?into??basketball_game_score_detail?values('B',3,'2020/8/28?9:44:18',3,'B3');
insert?into??basketball_game_score_detail?values('A',5,'2020/8/28?9:45:32',2,'A5');
insert?into??basketball_game_score_detail?values('B',5,'2020/8/28?9:46:46',3,'B5');

這里我使用了lead和lag來取每個(gè)組的前幾個(gè)值，這個(gè)和最大聯(lián)系天數(shù)不太一樣，但也可以用類似思路去解，但是使用lead和lag做起來更容易理解

select?distinct?a.name?,a.team?from
(
select?*,lead(name,1)?over(partition?by?team?order?by?score_time)?as?ld1
,lead(name,2)?over(partition?by?team?order?by?score_time)?as?ld2
,lag(name,1)?over(partition?by?team?order?by?score_time)?as?lg1
,lag(name,2)?over(partition?by?team?order?by?score_time)?as?lg2
from?table
)?a
where?(a.name?=a.ld1?and?a.name?=a.ld2)
or?(a.name?=a.ld1?and?a.name?=a.lg1)
or?(a.name=a.lg1?and?a.name=a.lg2)

第二小問面試時(shí)沒完全做出來，說了下思路，現(xiàn)在想了想當(dāng)時(shí)的思路還是有問題，而且這個(gè)題也并不難，核心還是記錄每個(gè)時(shí)刻的累計(jì)得分表

SELECT?TEAM,number,name,score_time,score,case?when?team='A'?then?score?else?0?end?as?A_score
,case?when?team='B'?then?score?else?0?end?B_score
FROM?basketball_game_score_detail
ORDER?BY?SCORE_time

如下得到每個(gè)時(shí)刻的累計(jì)得分表

select?team,number,name,score_time,A_score,b_score
,sum(A_score)over(order?by?score_time)?as??a_sum_score2
,sum(b_score)over(order?by?score_time)?as?b_sum_score2
from?
(
??SELECT?TEAM,number,name,score_time,score,case?when?team='A'?then?score?else?0?end?as?A_score
??,case?when?team='B'?then?score?else?0?end?B_score
??FROM?basketball_game_score_detail
??ORDER?BY?SCORE_time
)?as?x

計(jì)算每個(gè)時(shí)刻的累計(jì)得分差，和上個(gè)時(shí)間的累計(jì)得分差，只要兩個(gè)的符號(hào)相反就是反超時(shí)刻。感覺思路還是比較簡潔的。

select?*,score_gap*last_score_gap
from?
(
?select??*,a_sum_score2-b_sum_score2?as?score_gap?
?,lag(a_sum_score2-b_sum_score2,1)over(order?by?score_time)?as?last_score_gap
?from?
?(
??select?team,number,name,score_time,A_score,b_score
??,sum(A_score)over(order?by?score_time)?as??a_sum_score2
??,sum(b_score)over(order?by?score_time)?as?b_sum_score2
??from?(
???SELECT?TEAM,number,name,score_time,score,case?when?team='A'?then?score?else?0?end?as?A_score
???,case?when?team='B'?then?score?else?0?end?B_score
???FROM?basketball_game_score_detail
???ORDER?BY?SCORE_time
??)?as?x
?)?as?y
)?as?z
where?z.score_gap*last_score_gap<=0
and?a_sum_score2<>b_sum_score2?

當(dāng)然，不光會(huì)有SQL課程提供，我們還會(huì)有大廠數(shù)據(jù)大佬幫你答疑，包括技術(shù)、求職、面試，趕緊來吧！

文章來源知乎：絢麗的小海螺

這是我開發(fā)的機(jī)器人公眾號(hào)小號(hào)，目前增加了天氣查詢，955公司名單，關(guān)注時(shí)間查詢；后面還會(huì)增加圖片功能和每日送書抽獎(jiǎng)送書活動(dòng)，以及調(diào)戲功能，歡迎來體驗(yàn)，捧場(chǎng)。

推薦閱讀:
入門:?最全的零基礎(chǔ)學(xué)Python的問題? |?零基礎(chǔ)學(xué)了8個(gè)月的Python??|?實(shí)戰(zhàn)項(xiàng)目?|學(xué)Python就是這條捷徑

干貨:爬取豆瓣短評(píng)，電影《后來的我們》?|?38年NBA最佳球員分析?|? ?從萬眾期待到口碑撲街！唐探3令人失望? |?笑看新倚天屠龍記?|?燈謎答題王?｜用Python做個(gè)海量小姐姐素描圖?｜碟中諜這么火，我用機(jī)器學(xué)習(xí)做個(gè)迷你推薦系統(tǒng)電影

趣味:彈球游戲? |?九宮格? |?漂亮的花?|?兩百行Python《天天酷跑》游戲!

AI:?會(huì)做詩的機(jī)器人?|?給圖片上色?|?預(yù)測(cè)收入?|?碟中諜這么火，我用機(jī)器學(xué)習(xí)做個(gè)迷你推薦系統(tǒng)電影

小工具:?Pdf轉(zhuǎn)Word，輕松搞定表格和水??！?|?一鍵把html網(wǎng)頁保存為pdf！|??再見PDF提取收費(fèi)！?|?用90行代碼打造最強(qiáng)PDF轉(zhuǎn)換器，word、PPT、excel、markdown、html一鍵轉(zhuǎn)換?|?制作一款釘釘?shù)蛢r(jià)機(jī)票提示器！?｜60行代碼做了一個(gè)語音壁紙切換器天天看小姐姐！｜


年度爆款文案
1).臥槽！Pdf轉(zhuǎn)Word用Python輕松搞定！
2).學(xué)Python真香！我用100行代碼做了個(gè)網(wǎng)站，幫人PS旅行圖片，賺個(gè)雞腿吃
3).首播過億，火爆全網(wǎng)，我分析了《乘風(fēng)破浪的姐姐》，發(fā)現(xiàn)了這些秘密?
4).80行代碼！用Python做一個(gè)哆來A夢(mèng)分身?
5).你必須掌握的20個(gè)python代碼，短小精悍，用處無窮?
6).30個(gè)Python奇淫技巧集?
7).我總結(jié)的80頁《菜鳥學(xué)Python精選干貨.pdf》,都是干貨?
8).再見Python！我要學(xué)Go了！2500字深度分析！
9).發(fā)現(xiàn)一個(gè)舔狗福利！這個(gè)Python爬蟲神器太爽了，自動(dòng)下載妹子圖片

點(diǎn)閱讀原文，看B站我的視頻！