常見的SQL筆試題和面試題

來自專欄猴子聊數據分析

這是《怎樣使用SQL進行數據分析？》系列教材的第5篇《常見的SQL筆試和面試題》，想知道寫作初衷，看這裡：怎樣使用SQL進行數據分析？

• SQL語言允許使用通配符進行字元串匹配的操作，其中『%』可以表示：

A.零個字元

B.1個字元

C.多個字元

D.以上都是

• 如果有兩張表employees和departments如下：

（1）寫出SQL得出每個部門的平均工資。

（2）查詢量表得出如下結果：

• 如有有一張表player，有id、name兩個列，我想知道這張表有多少行數據，id最大的前10位的name是什麼，請問這兩個SQL語句如何寫？

• 查詢一張數據表（tb），基本欄位：日期，訂單

要求用SQL實現：

周次（week），訂單總和，日均訂單，極大值訂單，極小值訂單

• 使用SQL實現以下數據錶行轉列及總分，平均分（數據表：table）

• 查詢數據表（order），基本欄位如下：

省，市，店鋪名稱，訂單數

要求安」市」排序，需要看到每一個市的訂單排名前三名的門店及訂單數。

• 用戶登陸日誌表為user_id，log_time，session_id，plat

1.用sql查詢近30天，每天平均登陸用戶數量？

2.寫sql查詢出近30天，連續訪問7天以上的用戶數量？

• 交易表結構user_id（用戶ID），order_id（訂單），pay_time（付款時間），order_amount（金額）。

1.寫ql查詢過去一個月付款用戶量（提示：用戶量需去重）最高的3天分別是哪幾天？

2.寫sql查詢昨天每個用戶最後付款的訂單ID及金額？

• 有PV表a（表結構為user_id（用戶名），goods_id（商品ID）），點擊表b（表結構為user_id（用戶名），goods_id（商品ID））兩個表，各存放40億條user_id的goods_id訪問記錄，在防止數據傾斜的情況下，寫一句sql找出a、b兩個表共同的user_id與相應的goods_id？

• 對於通過不同渠道拉新進來的用戶，經過一段時間許多用戶可能流水，而留下來的用戶稱之為留存用戶。分析用戶留存是拉新和用戶運營的重要指標。假如我們有一張用戶訪問表：person_visit，記錄了所有用戶的訪問信息，包含欄位：

用戶id：user_id，訪問時間：visit_date，訪問頁面：page_name，訪問渠道（android,ios）：plat等

1.請統計近7天每天到訪的新用戶數。

2.請統計每個訪問渠道7天前（D-7）的新用戶的3日留存和7日留存率。

• 表group有四個欄位，表結構如下：

create table group(

id bigint comment 群號,

name string comment 群名,

class string comment 群類別,

num int comment 群成員數量

);

數據如下：

1 一起打球 籃球 10

2 來玩球吧 籃球 15

3 濱江一霸 籃球 5

4 足球小將 足球 20

5 絕代雙驕 足球 30

6 玩個球啊 乒乓 19

...

PK為id

1、求群數量少於1000的群類別

2、求每個群類別下，群成員數量最多的100個群

• 需要對用戶抽樣用以分析，用戶表結構為user_id（用戶ID），reg_time（註冊時間），age（年齡）。

1.寫一句SQL按user_id尾數隨機抽樣2000個用戶？

2.寫一句SQL取出按各年齡段（每10歲一個分段，如：[0,10),[10,20),[20,30)...）分別抽取1%的用戶？

• SQL語句性能優化方面的面試題，看這個:

MySql學習(七) -- 查詢性能優化 深入理解MySql如何執行查詢

如果上面的題目不會，建議按下面的內容進行學習或者複習：

怎樣使用SQL進行數據分析

推薦閱讀：