資料分析面試常見業務名額計算sql——日活/留存/連續登入

日活/月活

#日活
select login_data,count(distinct user_id)
from dm.login_table
group by login_data           

#月活
select extract(year_month  from login_data) as month,count(distinct user_id)
from dm.login_table
group by extract(year_month  from login_data)           

EXTRACT()函數用來提取時間，傳回日期/時間的單獨部分，比如年、月、日、小時、分鐘等等，如下：

1 SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
               EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
               EXTRACT(DAY FROM OrderDate) AS OrderDay,
               EXTRACT(year_month FROM OrderDate) AS ym
 FROM Orders           

每天新增使用者數/次留/7日留存/15日留存

首先，計算每個使用者第一次登入的時間（最早的登入時間），即使用者注冊時間表

#傳回使用者注冊時間表
select user_id,min(login_date) as register_date
from dm.login_table
group by user_id           

第二，計算留存

通過使用者id連接配接使用者首次登入時間表（注冊時間表）與登入時間表，然後按日期分組彙總每日首次登入的使用者數量（即新增使用者數）。

次日留存率的計算方式是，先判斷使用者注冊時間與登入時間差是不是1，如果是，就是次留的客戶，然後除以登入時間，進而得到次日留存率。

以此類推可計算7日留存率，15日留存率……

datediff(login_date,register_date)>0這個條件是用來判斷登入時間是不是晚于注冊時間

select
register_date,
 #計算每日新增使用者
count(distinct a.user_id) as new_user,
 #計算次日留存率
count(dinstinct if(datediff(login_date,register_date)=1,a.user_id,null))/count(distinct a.user_id) as remain_1,
 #計算7日留存率
 count(dinstinct if(datediff(login_date,register_date)=7,a.user_id,null))/count(distinct a.user_id) as remain_7,
#計算15日留存率
   count(dinstinct if(datediff(login_date,register_date)=15,a.user_id,null))/count(distinct a.user_id) as remain_15
from
(select user_id,min(login_date) as register_date
from dm.login_table
group by user_id) a 
left join dm.login_table b on a.user_id = b.user_id and datediff(login_date,register_date)>0 
group by register_date           

連續登陸——使用者最多連續登陸天數

第一步，按使用者id，登陸時間表，進行排序

select 
user_id,login_date,rank() over (partition by user_id order by login_date)
from dm.login_table           

連續登入的時間有如下特征比如10011這個使用者：

登入時間2022-08-07減去5等于2022-08-02，

登入時間2022-08-08減去5等于2022-08-02，

即登陸的時間減去排名總是等于同一日期的，就代表着他這兩天是連續登入的。

第二，判斷一個使用者是不是連續登入就是判斷結果是不是有重複日期，最大連續登入天數就是查找使用者登陸中重複日期最多的即可。

使用函數date_sub()對結果進行簡單變換處理，如下：

select 
user_id,
date_sub(login_date,interval rank() over (partition by user_id order by login_date) day) as primary_date
from dm.login_table           

運作結果

第三，對使用者登陸天數聚合，求得連續登陸天數

select user_id,primary_date,count(1) as cnt
from
(select
user_id,
date_sub(login_date,interval rank() over (partition by user_id order by login_date) day) as primary_date
from dm.login_table) t
group by user_id,primary_date           

第四，使用max()函數求使用者登陸日期中連續登陸天數中最大值

select user_id, max(cnt) as '連續登陸天數'
from
(select user_id,primary_date,count(1) as cnt
from
(select
user_id,
date_sub(login_date,interval rank() over (partition by user_id order by login_date) day) as primary_date
from dm.login_table) t
group by user_id,primary_date) t
group by user_id           

關于date_sub函數

DATE_SUB()函數接受兩個參數：
DATE_SUB(start_date,interval expr unit)
start_date是DATE或DATETIME的起始值。
expr是一個字元串，用于确定從起始日期減去的間隔值。unit是expr可解析的間隔機關，例如DAY，MONTH，HOUR等           

更多細節請參考：https://blog.csdn.net/qq_26898033/article/details/125208780