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

WITH TotalUIDs AS (  
    SELECT COUNT(DISTINCT uid) AS total_uids  
    FROM tb_user_log  
) --这里用临时表计算了用户的总数量
select
distinct user_grade,round(count(uid) over(partition by user_grade)/(SELECT total_uids FROM TotalUIDs),2) as ratio
from
(
	select
	t3.uid,
	case 
		when last_dt_diff<7 and first_dt_diff!=last_dt_diff then '忠实用户'
		when last_dt_diff<7 and first_dt_diff=last_dt_diff then '新晋用户'
		when last_dt_diff>=7 and last_dt_diff<30 then '沉睡用户'
		when last_dt_diff>=30 then '流失用户'
	end as user_grade
	from
	(
	select
	t1.uid,
	TIMESTAMPDIFF(DAY,t1.dt,'2021-11-04') as first_dt_diff,--主要是计算一个用户的最近登录和最远登录
	TIMESTAMPDIFF(DAY,t2.dt,'2021-11-04') as last_dt_diff
	from
	(
	select
	uid,min(date_format(in_time,'%Y-%m-%d')) dt
	from
	tb_user_log 
	group by uid
	) t1
	left join 
	(
	select
	uid,max(date_format(out_time,'%Y-%m-%d')) dt
	from
	tb_user_log 
	group by uid
	) t2
	on t1.uid=t2.uid
	) t3
) t4