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

-- 统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序。
-- 忠实用户(近7天活跃过且非新晋用户)——最大日期的差值 与 用户最大活跃日期 差值 0-6  且用户最小活跃日期与最大日期差值大于6 
-- 新晋用户(近7天新增)——最大活跃日期 与 用户最小活跃日期  差值 0-6 
-- 沉睡用户(近7天未活跃但更早前活跃过)——最大活跃日期 与 用户最大活跃日期  差值 大于6 
-- 流失用户(近30天未活跃但更早前活跃过)——最大活跃日期 与 用户最大活跃日期  差值 大于29
-- 假设今天就是数据中所有日期的最大值。近7天表示包含当天T的近7天，即闭区间[T-6, T]。
-- 获取每个用户对应的最小日期、最大日期、整体最大日期
with A as (
select  
uid 
,max(date(in_time)) as u_max_dt 
,min(date(in_time)) as u_min_dt 
,(select max(date(in_time)) from tb_user_log) as max_dt 
from 
tb_user_log 
group by uid ) 

select 
user_grade
,round(count(1)/(select count(1) from A),2) as ratio 
from (
select 
uid
,(case 
when datediff(max_dt,u_max_dt) > 29 then '流失用户' 
when datediff(max_dt,u_max_dt) between 7 and 29 then '沉睡用户' 
when datediff(max_dt,u_min_dt) between 0 and 6  then '新晋用户' 
else '忠实用户' 
end ) as user_grade 
from A ) B 
GROUP BY user_grade 
order by ratio desc