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

# 近7天新增用户：按每个用户分组，计算最小的日期（min）。用datediff挑选6天及以内的用户作为新增用户
# 近7天、7-30天、30天及以上活跃过用户：按每个用户分组，计算最大的日期（max）。用datediff挑选6天及以内、7-29天以内、30天以上登陆过的用户

select t8.type_last
,round(count(*)/(select count(distinct uid) from tb_user_log),2) 
from (
select t7.uid,
(case when type_t6 is not null then type_t6 else type_t5 end ) as type_last 
from (
select t5.uid,t5.type as type_t5,t6.type as type_t6 from (
# 挑选近7天、7-30天、30天以上的活跃用户
select distinct t4.uid
,(case when (datediff(t4.max_time,t4.max_intime) > 6 and datediff(t4.max_time,t4.max_intime) <=29) then '沉睡用户' 
  when datediff(t4.max_time,t4.max_intime) > 29 then '流失用户' else '忠实用户' end) as type 
from (
select t3.uid
,(select max(in_time) from tb_user_log) as max_time
,max(t3.in_time) over(partition by t3.uid) as max_intime 
from tb_user_log as t3 ) t4) t5
left join 
(# 挑选近七天新增用户
select distinct t2.uid,'新晋用户' as type from (
select t1.uid
    ,(select max(in_time) from tb_user_log) as max_time 
    ,min(t1.in_time) over(partition by t1.uid) as min_time 
from tb_user_log as t1 ) t2 where datediff(t2.max_time,t2.min_time) <= 6 ) as t6 
on t5.uid=t6.uid ) t7 ) t8 group by t8.type_last 
order by round(count(*)/(select count(distinct uid) from tb_user_log),2) desc,t8.type_last