本篇節(jié)選自書籍《對比Excel，輕松學(xué)習(xí)SQL數(shù)據(jù)分析》一書，主要講解數(shù)據(jù)分析面試中常見的30道SQL面試題。

1-20道可以看：

30道經(jīng)典SQL面試題講解(1-10)

30道經(jīng)典SQL面試題講解(11-20)

21 獲取新增用戶數(shù)

現(xiàn)在有一個用戶表user_reg_table，這張表存儲了每位用戶的uid(用戶id)、reg_time(注冊時間)等其他信息，我們想知道某一天的新增用戶數(shù)，以及該天對應(yīng)的過去7天內(nèi)每天平均新增用戶數(shù)，該怎么實現(xiàn)呢？

user_reg_table表如下所示：

自己先想一下代碼怎么寫，然后再參考我的代碼。

set @day_date = "2020-01-01";

select
    count(if(date(reg_time) = @day_date,uid,null)) as new_cnt
    ,count(uid)/7 as 7_avg_cnt
from 
    demo.user_reg_table
where 
 date(reg_time) between date_sub(@day_date,interval 6 day) and @day_date

解題思路：

我們是想知道某一天的用戶數(shù)，這個某一天是一個可變的值，所以我們想到了變量，通過設(shè)置變量來達到日期的變化；其次我們還需要過去7天，在變量的基礎(chǔ)上減去6天即可，這里面需要注意的是，我們用的between用來篩選介于過去7天和今天之間的用戶，而不能直接使用大于7天前日期的這個條件，因為大于7天前的日期很有可能包括你設(shè)置的變量后面的日期。最后運行結(jié)果如下：

22 獲取用戶首次購買時間

現(xiàn)在我們有一張表first_order_table，這張表中包含了order_id(訂單id)、uid(用戶id)、order_time(訂單時間)，我們想知道每個用戶的首次購買時間，以及是否在最近7天內(nèi)，該怎么實現(xiàn)呢？

first_order_table表如下所示：

自己先想一下代碼怎么寫，然后再參考我的代碼。

select
    t1.uid
    ,t1.first_time
    ,(date(t1.first_time) > date_sub(curdate(),interval 6 day)) is_7_day
from
    (select
        uid
        ,min(order_time) first_time
    from
        demo.first_order_table
    group by
        uid
    )t1

解題思路：

我們主要有兩個事情，第一件事就是獲取每個用戶的首次購買時間，其實就是最小時間，然后再對最小時間和最近7天進行比較，得出首次購買時間是否在最近7天。最后運行結(jié)果如下：

23 同時獲取用戶和訂單數(shù)據(jù)

還是前面的兩張表user_reg_table和first_order_table，現(xiàn)在我們想知道過去7天每天的新增用戶數(shù)、訂單數(shù)、下單用戶數(shù)，該怎么實現(xiàn)呢？

自己先想一下代碼怎么寫，然后再參考我的代碼。

set @day_date = "2020-01-04";

select
    t1.tdate
    ,t1.new_cnt
    ,t2.order_cnt
    ,t2.uid_cnt
from
    (
    select
        date(reg_time) tdate
        ,count(uid) new_cnt
    from
        demo.user_reg_table
    where
        date(reg_time) between date_sub(@day_date,interval 6 day) and @day_date
    group by 
        date(reg_time)
    )t1
left join
    (
    select
        date(order_time) tdate
        ,count(order_id) order_cnt
        ,count(distinct uid) uid_cnt
    from
        demo.first_order_table
    where
        date(order_time) between date_sub(@day_date,interval 6 day) and @day_date
    group by 
        date(order_time)
    )t2
on t1.tdate = t2.tdate

解題思路：

我們要獲取每天的新增用戶數(shù)以及訂單數(shù)，新增用戶數(shù)和訂單數(shù)據(jù)是存儲在兩個不同的表中，所以我們可以先分別獲取每天的新增用戶數(shù)和每天的訂單數(shù)，然后再根據(jù)日期把兩個表拼接在一起。最后運行結(jié)果如下：

24 隨機抽樣

還是前面的兩張表user_reg_table和first_order_table，現(xiàn)在我們想要從用戶表中隨機抽取5位用戶，以及這5位用戶的歷史購買訂單數(shù)，想想該怎么實現(xiàn)呢？

自己先想一下代碼怎么寫，然后再參考我的代碼。

select
    user_table.uid
    ,t.order_cnt
from
    demo.user_reg_table user_table
left join
    (
    select
        uid
        ,count(order_id) as order_cnt
    from
        demo.first_order_table
    group by
        uid
    )t
on user_table.uid = t.uid
order by rand()
limit 5

解題思路：

我們要隨機獲取5位用戶的歷史購買訂單數(shù)，首先需要生成每個用戶歷史的購買訂單數(shù)，然后再從中隨機抽取5位。具體的隨機抽取規(guī)則為：利用rand()生成隨機數(shù)，然后再利用order by進行排序，最后利用limit把前5條顯示出來。最后運行結(jié)果如下：

25 獲取沉默用戶數(shù)

還是前面的兩張表user_reg_table和first_order_table，現(xiàn)在我們想獲取沉默用戶的數(shù)量，沉默的定義是已注冊但是最近30天內(nèi)沒有購買記錄的人，該怎么實現(xiàn)呢？

自己先想一下代碼怎么寫，然后再參考我的代碼。

select
    count(user_table.uid) chenmo_cnt
from
    demo.user_reg_table user_table
left join
    (
    select
        uid
    from
        demo.first_order_table 
    where
        date(order_time) < date_sub(curdate(),interval 29 day)
    group by
        uid
    )t
on user_table.uid = t.uid
where
    t.uid is null

解題思路：

我們要獲取近30天沒有購買記錄的人，可以先把最近30天內(nèi)有購買記錄的人取出來，然后用user_table表中的uid去拼接最近30天有購買記錄的人，如果不能拼接到，即拼接結(jié)果為null，就表示這部分人最近30天沒有購買。把null的部分取出來，然后對uid進行計數(shù)即可。最后運行結(jié)果為14，因為我們是用的curdate()，所以不同時間運行得到的結(jié)果會是不一樣的。

26 獲取新用戶的訂單數(shù)

還是前面的兩張表user_reg_table和first_order_table，現(xiàn)在我們想獲取最近7天注冊新用戶在最近7天內(nèi)的訂單數(shù)是多少，該怎么實現(xiàn)呢？

自己先想一下代碼怎么寫，然后再參考我的代碼。

select
    sum(t2.order_cnt)
from
    (
    select
        uid
    from
        demo.user_reg_table
    where
        date(reg_time) > date_sub(curdate(),interval 6 day)
    )t1
left join
    (
    select
        uid
        ,count(order_id) order_cnt
    from
        demo.first_order_table
    where
        date(order_time) > date_sub(curdate(),interval 6 day)
    group by
        uid
    )t2
on t1.uid = t2.uid

解題思路：

我們要獲取最近7天注冊新用戶在最近7天內(nèi)的訂單數(shù)，首先獲取最近7天新注冊的用戶，然后獲取每個用戶在最近7天內(nèi)的訂單數(shù)，最后將兩個表進行拼接，且新用戶表為主表，進行左連接。最后運行結(jié)果為14，不同時間運行得到的結(jié)果會是不一樣的。

27 獲取借款到期名單

現(xiàn)在有一張借款表loan_table,這張表記錄了每筆借款的id、loan_time(借款時間)、expire_time(到期時間)、reback_time(還款時間)、amount(金額)、status(還款狀態(tài),1表示已還款、0表示未還款)，我們想要獲取每天到期的借款筆數(shù)、借款金額和平均借款天數(shù)，該怎么實現(xiàn)呢？

loan_table表如下所示：

自己先想一下代碼怎么寫，然后再參考我的代碼。

select
    count(id) as loan_cnt
    ,sum(amount) as loan_amount
    ,avg(datediff(reback_time,loan_time)) avg_day
from
    demo.loan_table
where
    expire_time = curdate()

解題思路：

我們是要獲取每天到期的數(shù)據(jù)，只需要通過篩選到期時間等于當天把當天到期的數(shù)據(jù)篩選出來，然后對id進行計數(shù)得到到期筆數(shù)，對amount進行求和得到到期金額，對還款時間和借款時間做差取平均得到平均借款天數(shù)，注意這里是用的還款時間和借款時間做差，而非到期時間和借款時間做差，因為有可能提前還款或逾期。最后運行結(jié)果為空，表示今天沒有到期的借款。

28 獲取即將到期的借款信息

還是前面的借款表loan_table，現(xiàn)在我們想知道有多少筆借款會在未來7天內(nèi)到期，其中有多少筆是已經(jīng)還款的，該怎么實現(xiàn)呢？

自己先想一下代碼怎么寫，然后再參考我的代碼。

select
    count(id) as loan_cnt
    ,count(if(status = 1,id,null)) as reback_cnt
from
    demo.loan_table
where
    expire_time between curdate() and date_sub(curdate(),interval 6 day)

解題思路：

我們是要獲取未來7天內(nèi)要到期的借款筆數(shù)和其中已經(jīng)還款的筆數(shù)，首先把最近7天內(nèi)要到期的數(shù)據(jù)篩選出來，然后再通過還款狀態(tài)status進行判斷，再獲取已還款的筆數(shù)。最后運行結(jié)果為空。

29 獲取歷史逾期借款信息

還是前面的借款表loan_table，現(xiàn)在我們想知道歷史逾期的筆數(shù)和金額以及至今還逾期的筆數(shù)和金額，該怎么實現(xiàn)呢？

自己先想一下代碼怎么寫，然后再參考我的代碼。

select
    count(id) as loan_cnt
    ,sum(amount) as loan_amount
    ,count(if(status = 0,id,null)) as no_reback_cnt
    ,sum(if(status = 0,amount,0)) as no_reback_amount
from
    demo.loan_table
where
    (reback_time > expire_time)
    or (reback_time is null and expire_time < curdate())

解題思路：這里面的關(guān)鍵信息在于逾期怎么判斷，逾期是用到期時間和還款時間去進行比較，如果是逾期且現(xiàn)在已經(jīng)還款了的，可以直接比較到期時間和還款時間，如果還款時間大于到期時間，說明是逾期的；還有一種是逾期且現(xiàn)在還未還款的，這種情況是沒有還款時間的，也就是還款時間是空，但是到期時間是在今天之前，說明已到期但是未還款。最后運行結(jié)果如下：

30 綜合實戰(zhàn)

這一題是我們最后一道實戰(zhàn)題，給大家還原一下我們在前面梳理數(shù)據(jù)庫邏輯的時候遇到的情況。假如你現(xiàn)在剛?cè)肼氁患倚碌碾娚坦荆阈枰ㄟ^一個Sql把電商整個漏斗轉(zhuǎn)化環(huán)節(jié)的數(shù)據(jù)全部取出來：主要當日總瀏覽量、瀏覽人數(shù)、加購物車數(shù)、加購物車人數(shù)、訂單數(shù)、下單人數(shù)、確認收貨訂單數(shù)，該怎么寫。已知有如下幾張表：

browse_log_table(瀏覽記錄表)：id(瀏覽id)、product_id(商品id)、uid(用戶id)、channel(渠道)、browse_time(瀏覽時間)......；

cart_table(購物車詳情表)：id(購物車id)、browse_id(瀏覽id)、cart_time(加購物車時間)......；

order_table(訂單詳情表)：id(訂單id)、cart_id(購物車id)、order_time(訂單時間)、amount(訂單金額)......；

take_table(收貨詳情表)：order_id(訂單id)、take_time(確認收貨時間)......。

select
    count(browse_log_table.id) as browse_cnt
    ,count(distinct browse_log_table.uid) as browse_uid_cnt
    ,count(cart_table.id) as cart_cnt
    ,count(distinct if(cart_table.id is not null,browse_log_table.uid,null)) as cart_uid_cnt
    ,count(order_table.id) as order_cnt
    ,count(distinct if(order_table.id is not null,browse_log_table.uid,null)) as order_uid_cnt
    ,count(take_table.id) as take_cnt
    ,count(distinct if(take_table.id is not null,browse_log_table.uid,null)) as take_uid_cnt
from
    browse_log_table
left join
    cart_table
on browse_log_table.id = cart_table.browse_id
left join
    order_table
on cart_table.id =  order_table.cart_id
left join
    take_table
on order_table.id = take_table.order_id
where 
    browse_log_table.browse_time = curdate()

想進一步了解更多內(nèi)容的同學(xué)，可以閱讀《對比Excel，輕松學(xué)習(xí)SQL數(shù)據(jù)分析》一書。

▊《對比Excel，輕松學(xué)習(xí)SQL數(shù)據(jù)分析》

張俊紅 著

學(xué)習(xí)SQL 的主要原因是工作需要。網(wǎng)上關(guān)于數(shù)據(jù)相關(guān)崗位的招聘都要求有熟練使用SQL 這一條，為什么會這樣呢？這是因為我們負責的是與數(shù)據(jù)相關(guān)的工作，而獲取數(shù)據(jù)是我們工作的第一步，比如，你要通過數(shù)據(jù)做決策，但是現(xiàn)在公司的數(shù)據(jù)基本上不存儲在本地Excel 表中，而是存儲在數(shù)據(jù)庫中，想要從數(shù)據(jù)庫中獲取數(shù)據(jù)就需要使用SQL，所以熟練使用SQL 成了數(shù)據(jù)相關(guān)從業(yè)者入職的必要條件。本書的所有代碼和函數(shù)均以MySQL 8.0 為主。

（掃碼了解本書詳情）

     

      
      

     


     

      如果喜歡本文
     
     

      歡迎 在看丨留言丨分享至朋友圈 三連
     
     

      

     
     

      

       

        

         

          

           

          
          

           
 熱文推薦  
          
         
        
        

         

          


          

           
數(shù)據(jù)營銷“教父”宋星十年傾心之作

           
如何通過XMind 實踐OKR 工作法

           
你好，這是微視AI還原的李煥英

           
從產(chǎn)品經(jīng)理到產(chǎn)品架構(gòu)師

          
         
        
       
      
      

       


      
     
     


     

     

      ▼點擊閱讀原文，獲取本書詳情~