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

思路：

分别以7，30天为节点划分成三个区间：

区间：A:[0,7], B:[8:30], C:[31,+)

• 新晋用户：只在A区间而不再B、C区间；
• 忠实用户：在A区间，但也在B或C区间；
• 沉睡用户：不在A、C区间，只在B区间；
• 流失用户：不在A、B区间，只在C区间。

select user_grade,round(uv_cut/uv,2) as ratio
from (
	select user_grade,
				 count(user_grade) as uv_cut
	from (
			select distinct uid,
						 case when dt7 is not null and dt30 is null and dt30up is null then '新晋用户'
									when dt7 is not null and (dt30 is not null or dt30up is not null) then '忠实用户'
									when dt7 is null and dt30 is not null and dt30up is null then '沉睡用户'
									when dt7 is null and dt30 is null and dt30up is not null then '流失用户'
									end as user_grade
			from (
					select t0.uid,
								 max_date,
								 date(t0.in_time) as dt,
								 date(t1.in_time) as dt7,
								 date(t2.in_time) as dt30,
								 date(t3.in_time) as dt30up
					from tb_user_log as t0
					left join (select max(date(in_time)) as max_date
										from tb_user_log) as t on 1
					left join tb_user_log as t1 on t1.uid=t0.uid and datediff(max_date,date(t1.in_time))<=6
					left join tb_user_log as t2 on t2.uid=t0.uid and datediff(max_date,date(t2.in_time))<=29 and datediff(max_date,date(t2.in_time))>=7
					left join tb_user_log as t3 on t3.uid=t0.uid and datediff(max_date,date(t3.in_time))>=30
					order by uid
			) as a
	) as b
	group by user_grade
) as c
left join (select count(distinct uid) as uv
								 from tb_user_log) as t4 on 1
order by ratio desc