题解 | #统计活跃间隔对用户分级结果#

with t1 as(
# 1. 新晋用户(近7天新增)，用户注册表
select uid, min(date_format(in_time,"%Y-%m-%d")) as login from tb_user_log
group by uid
having datediff((select max(date_format(out_time,"%Y-%m-%d")) from tb_user_log),login) < 7
), t2 as(
# 2. 忠实用户(近7天活跃过且非新晋用户)，用户在线表，剔除t1
select * from(
(select uid, date_format(in_time,"%Y-%m-%d") as dt from tb_user_log)
union
(select uid, date_format(out_time,"%Y-%m-%d") as dt from tb_user_log)
) t
where datediff((select max(date_format(out_time,"%Y-%m-%d")) from tb_user_log),dt) < 7 and uid not in (select uid from t1)
), t3 as(
# 3. 沉睡用户(近7天未活跃但更早前活跃过)
select * from(
(select uid, date_format(in_time,"%Y-%m-%d") as dt from tb_user_log)
union
(select uid, date_format(out_time,"%Y-%m-%d") as dt from tb_user_log)
) t
where datediff((select max(date_format(out_time,"%Y-%m-%d")) from tb_user_log),dt) >= 7 and
datediff((select max(date_format(out_time,"%Y-%m-%d")) from tb_user_log),dt) <30 and uid not in (select uid from t2)
), t4 as(
# 4. 流失用户(近30天未活跃但更早前活跃过)
select * from(
(select uid, date_format(in_time,"%Y-%m-%d") as dt from tb_user_log)
union
(select uid, date_format(out_time,"%Y-%m-%d") as dt from tb_user_log)
) t
where datediff((select max(date_format(out_time,"%Y-%m-%d")) from tb_user_log),dt) >= 30 and
uid not in (select uid from t2) and uid not in (select uid from t3)
)

(select '新晋用户' as user_grade, round(count(distinct uid)/(select count(distinct uid) from tb_user_log),2) as ratio from t1)
union
(select '忠实用户' as user_grade, round(count(distinct uid)/(select count(distinct uid) from tb_user_log),2) as ratio from t2)
union
(select '沉睡用户' as user_grade, round(count(distinct uid)/(select count(distinct uid) from tb_user_log),2) as ratio from t3)
union
(select '流失用户' as user_grade, round(count(distinct uid)/(select count(distinct uid) from tb_user_log),2) as ratio from t4)
order by ratio desc

select user_grade, 
round(count(uid)/(select count(distinct uid) from tb_user_log),2) as ratio 
from(
# 2. 查询最近一次登陆时间与max_time之差,注册时间与max_time之差来分组
select uid, case
when datediff((select max(in_time) from tb_user_log),last_time)<7 and datediff((select max(in_time) from tb_user_log),first_time)<7 then '新晋用户'
when datediff((select max(in_time) from tb_user_log),last_time)<7 and datediff((select max(in_time) from tb_user_log),first_time)>=7 then '忠实用户'
when datediff((select max(in_time) from tb_user_log),last_time)>=7 and datediff((select max(in_time) from tb_user_log),last_time)<30 then '沉睡用户'
else '流失用户'
end as user_grade
from (
# 1. 查询每个用户最近一次的登陆时间和用户注册时间
select uid, 
date_format(max(in_time),'%Y-%m-%d') as last_time,
date_format(min(in_time),'%Y-%m-%d') as first_time
from tb_user_log
group by uid
) t
) t1
group by user_grade
order by ratio desc