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

-- 求用户等级以及对应的比例 
select user_grade ,round(count(uid) / max(user_cnt),2) as ratio 
from (
-- 使用case when对用户进行分层
select uid ,user_cnt ,
case 
when last_dt_diff >= 30 then "流失用户" 
when last_dt_diff >= 7 then "沉睡用户"
when first_dt_diff < 7 then "新晋用户"
else "忠实用户" 
end as user_grade  
from (
-- 分别计算每个用户最小日期、最大日期和当前最大日期的差值
select uid ,user_cnt ,timestampdiff(day,first_dt,cur_dt) as first_dt_diff , timestampdiff(day,last_dt,cur_dt) as last_dt_diff  
from (
-- 获取用户对应的最小日期和最大日期,作为子表t1
select uid ,min(date(in_time)) as first_dt ,max(date(in_time)) as last_dt 
from tb_user_log 
group by uid ) t1
-- 左连接t1和t2,即将全表统计信息追加到每一行上，t1 left join t2 on 1
left join (
-- 获取当前日期和总用户数,作为子表t2
select max(date(in_time)) as cur_dt , count(distinct uid) as user_cnt 
from tb_user_log ) t2 on 1 
) t3 
) t4 
group by user_grade 
order by ratio desc