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

select tt.user_grade, round(cnt/(sum(cnt) over()),2) ratio
from (
	select t.level user_grade,count(*) cnt
	from ( -- 对用户进行分类
			select 
			uid,
			case when (datediff(M_time,max_time) <= 6 and datediff(M_time,min_time)>=7) then '忠实用户' -- 近7天活跃：今天-最近活跃日期 <=6,因为包含了当天；非新晋用户：今天-最早活跃时间>=7
			when datediff(M_time,min_time)<=6 then '新晋用户' -- 一定要算6，因为包含当天
			when (datediff(M_time,max_time)>=7 and datediff(M_time,max_time)<30) then '沉睡用户' 
			else '流失用户'
			end level
		from 
		(-- 用户最早进入时间，最近活跃时间，“今天”即所有日期的最大日期
		select 
			uid,
			min(date(in_time)) min_time, -- 最早活跃时间
			max(date(in_time)) max_time, -- 最近活跃时间
		,	(select max(date(in_time)) from tb_user_log) M_time -- “今天”
		from tb_user_log
		group by uid
		) tb
	)t
	group by t.level
) tt
group by tt.user_grade
order by ratio desc -- 按比例降序