【面試題】

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

用戶id：用戶唯一標識；

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

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

啟動次數(shù)：某一天中啟動了某應用多少次。

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

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

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

指標定義：

某日活躍用戶數(shù)，某日活躍的去重用戶數(shù)。

N日活躍用戶數(shù)，某日活躍的用戶數(shù)在之后的第N日活躍用戶數(shù)。

N日活躍留存率，N日留存用戶數(shù)/某日活躍用戶數(shù)

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

所需獲得的結(jié)果格式如下：

【解題思路】

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

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

1.每天的活躍用戶數(shù)

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

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

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

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

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

查詢結(jié)果如下：

2.?次日留存用戶數(shù)

再來看查詢結(jié)果中的次日留存用戶數(shù)

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

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

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

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

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

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

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

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

代入上面的sql就是：

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

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

3.次日留存率

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

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

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

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

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

查詢結(jié)果：

4.三日的留存數(shù)，三日留存率,七日的留存數(shù),七日留存率

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

最終sql代碼如下：

select?a.登陸時間,count(distinct?a.用戶id)?as?活躍用戶數(shù),count(distinct when 時間間隔=1 then 用戶id else null end) as 次日留存數(shù),count(distinct?when?時間間隔=1?then?用戶id?else?null end)?as??次日留存數(shù)?/?count(distinct?a.用戶id)?as?次日留存率,count(distinct?when?時間間隔=3?then?用戶id?else?null?end)?as??三日留存數(shù),count(distinct?when?時間間隔=3?then?用戶id?else?null?end)?as??三日留存數(shù)?/?count(distinct?a.用戶id)?as?三日留存率,count(distinct?when?時間間隔=7?then?用戶id?else?null?end)?as??七日留存數(shù),count(distinct?when?時間間隔=7?then?用戶id?else?null?end)?as??七日留存數(shù)?/?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.登陸時間;

查詢結(jié)果：

【本題考點】

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

2.靈活使用case來統(tǒng)計when 函數(shù)與group by 進行自定義列聯(lián)表統(tǒng)計。

3.遇到只有一個表，但是需要計數(shù)時間間隔的問題，就要想到用自聯(lián)結(jié)來求時間間隔，類似的有找出連續(xù)出現(xiàn)N次的內(nèi)容、滴滴2020求職真題。

【舉一反三】

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

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

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

2.2019年1-4月產(chǎn)生訂單的用戶，以及在次月的留存用戶數(shù)

【解題思路】?

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

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

select?b.性別,b.age, count(distinct?a.用戶id) as 用戶數(shù), 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月產(chǎn)生訂單的用戶，以及在次月的留存用戶數(shù)

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

(2)用自聯(lián)結(jié)計算時間間隔case when 計算符合個數(shù)并得出列的值。

select a.用戶id,count(case?when?timestampdiff(month,b.時間,a.時間)=1?then?a.用戶id?else?null?end)??as?次月留存用戶數(shù)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？

總結(jié)

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

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。