数据分析面试常见业务指标计算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