【面試題】

手機中的相機是深受大家喜愛的應用之一，下圖是某手機廠商數據庫中的用戶行為信息表中部分數據的截圖。

用戶id：用戶唯一標識；

應用名稱：是手機中的某個應用，例如相機、微信、大眾點評等。

啟動時長：某一天中使用某應用多長時間(分鐘)。

啟動次數：某一天中啟動了某應用多少次。

登陸時間：使用手機的日期。例如2018-05-01。

現在該手機廠商想要分析手機中的應用(相機)的活躍情況，需統計如下數據：

某日活躍用戶(用戶id)在后續的一周內的留存情況(計算次日留存用戶數，3日留存用戶數，7日留存用戶數)

指標定義：

某日活躍用戶數，某日活躍的去重用戶數。

N日活躍用戶數，某日活躍的用戶數在之后的第N日活躍用戶數。

N日活躍留存率，N日留存用戶數/某日活躍用戶數

例：登陸時間(20180501日)去重用戶數10000，這批用戶在20180503日仍有7000人活躍，則3日活躍留存率為7000/10000=70%

所需獲得的結果格式如下：

【解題思路】

本題中指標(用戶留存數、留存率)是《猴子 業務指標》中講過的常見業務指標，體現了某應用吸引用戶的能力。

該業務分析要求查詢結果中包括：日期(說明是按每天來匯總數據)、用戶活躍數、N日留存數、N日留存率。

1.每天的活躍用戶數

先來看活躍用戶數這一列如何分析出？

活躍用戶數對應的日期，表示每一行記錄的是當天的活躍用戶數。

當有“每個”出現的時候，要想到《猴子 從零學會SQL》中講過的用分組匯總來實現該業務問題。

按每天(登陸時間)分組(group by )，統計應用(相機)每天的活躍用戶數(計數函數count)。

select 登陸時間,count(distinct 用戶id) as 活躍用戶數 from 用戶行為信息表 where 應用名稱 ='相機' group by 登陸時間;

查詢結果如下：

2.?次日留存用戶數

再來看查詢結果中的次日留存用戶數

次日留存用戶數：在今日登錄，明天也有登錄的用戶數。也就是時間間隔=1。

一個表如果涉及到時間間隔，就需要用到自聯結，也就是將兩個相同的表進行聯結。

select?a.用戶id,a.登陸時間,b.登陸時間from?用戶行為信息表?as?a??left?join?用戶行為信息表?as?bon a.用戶id = b.用戶idwhere?a.應用名稱=?'相機';

聯結后的臨時表記為表c，那么如何從表c中查找出時間間隔(明天登陸時間-今天登陸時間)=1的數據呢？

(1)這涉及到計算兩個日期之間的差值，《猴子 從零學會sql》里講到對應單函數是timestampdiff。下圖是這個函數的用法。

select?*,timestampdiff(day,a.登陸時間,b.登陸時間)?as?時間間隔from?c;

用case語句選出時間間隔=1的數據，并計數就是次日留存用戶數

count(distinct case?when?時間間隔=1?then?用戶id?????else?null end) as 次日留存數

代入上面的sql就是：

select *,count(distinct when 時間間隔=1 then 用戶id?????else?null end) as 次日留存數?from(select?*,timestampdiff(day,a.登陸時間,b.登陸時間)?as?時間間隔from?c);

將臨時表c的sql代入上面就得到了查詢結果如下：

3.次日留存率

留存率=新增用戶中登錄用戶數/新增用戶數，所以次日留存率=次日留存用戶數/當日用戶活躍數

當日活躍用戶數是count(distinct 用戶id)

在上面分析次日留存數中，用次日留存用戶數/當日用戶活躍數就是次日留存率

select *,count(distinct when 時間間隔=1 then 用戶id else null end) as 次日留存數 / count(distinct 用戶id) as 次日留存率 from(select *,timestampdiff(day,a.登陸時間,b.登陸時間) as 時間間隔from c);

將臨時表c的sql代入就是：

查詢結果：

4.三日的留存數，三日留存率,七日的留存數,七日留存率

和次日留存用戶數，次日留存率分析思路一樣，只需要更改時間間隔=N(日留存)即可。

最終sql代碼如下：

select?a.登陸時間,count(distinct?a.用戶id)?as?活躍用戶數,count(distinct when 時間間隔=1 then 用戶id else null end) as 次日留存數,count(distinct?when?時間間隔=1?then?用戶id?else?null end)?as??次日留存數?/?count(distinct?a.用戶id)?as?次日留存率,count(distinct?when?時間間隔=3?then?用戶id?else?null?end)?as??三日留存數,count(distinct?when?時間間隔=3?then?用戶id?else?null?end)?as??三日留存數?/?count(distinct?a.用戶id)?as?三日留存率,count(distinct?when?時間間隔=7?then?用戶id?else?null?end)?as??七日留存數,count(distinct?when?時間間隔=7?then?用戶id?else?null?end)?as??七日留存數?/?count(distinct?a.用戶id)?as?七日留存率 from(select *,timestampdiff(day,a.登陸時間,b.登陸時間) as 時間間隔from?(select a.用戶id,a.登陸時間,b.登陸時間from 用戶行為信息表 as a left join 用戶行為信息表 as bon a.用戶id = b.用戶idwhere a.應用名稱= '相機') as c)?as?dgroup?by?a.登陸時間;

查詢結果：

【本題考點】

1.常用指標的理解，例如留存用戶數、留存率。

2.靈活使用case來統計when 函數與group by 進行自定義列聯表統計。

3.遇到只有一個表，但是需要計數時間間隔的問題，就要想到用自聯結來求時間間隔，類似的有找出連續出現N次的內容、滴滴2020求職真題。

【舉一反三】

鏈家2018春招筆試面試：現有訂單表和用戶表，格式字段如下圖：

訂單表	時間	訂單id	商品id	用戶id	訂單金額
用戶表	用戶id	姓名	性別	年齡

1.查詢2019年Q1季度，不同性別，不同年齡的成交用戶數，成交量及成交金額

2.2019年1-4月產生訂單的用戶，以及在次月的留存用戶數

【解題思路】?

1.查詢2019年Q1季度，不同性別，不同年齡的成交用戶數，成交量及成交金額

根據性別、年齡進行分組，利用多表連接及聚合函數求出成交用戶數，成交量及成交金額。

select?b.性別,b.age, count(distinct?a.用戶id) as 用戶數, count(訂單id), sum(a.訂單金額)from 訂單表 as a inner join 用戶表 as bon?a.用戶id?=?b.用戶idwhere a.時間 between '2019-01-01' and '2019-03-31'group by b.性別,b.age;?

2.2019年1-4月產生訂單的用戶，以及在次月的留存用戶數

(1)用時間函數(timestampdiff)計算時間間隔，本題要求月份差，即用month

(2)用自聯結計算時間間隔case when 計算符合個數并得出列的值。

select a.用戶id,count(case?when?timestampdiff(month,b.時間,a.時間)=1?then?a.用戶id?else?null?end)??as?次月留存用戶數from 訂單表 as a inner join 訂單表 as bon a.用戶id = b.用戶idwhere a.時間 between '2019-01-01' and '2019-04-30'group by a.用戶id

推薦：如何從零學會sql？

總結

以上是生活随笔為你收集整理的sql计算留存_链家面试题：如何分析留存率？的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。