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

WITH t1 AS(
SELECT 
		CASE WHEN DATEDIFF(now_dt,new_dt)<=6 THEN '新晋用户'  #近7天新增
		      WHEN DATEDIFF(now_dt,max_dt)<=6 AND DATEDIFF(now_dt,new_dt)>6 THEN'忠实用户' #近7天活跃过且非新晋用户
              WHEN DATEDIFF(now_dt,max_dt) BETWEEN 7 AND 29 THEN '沉睡用户'
		      ELSE '流失用户' END user_grade
FROM (
	SELECT uid, MAX(DATE(out_time)) max_dt,MIN(DATE(in_time)) new_dt,MAX(MAX(DATE(out_time)))OVER() now_dt
	FROM tb_user_log GROUP BY uid) act_table
)-- t1表  
SELECT user_grade,ROUND(COUNT(user_grade)/SUM(COUNT(user_grade))OVER(),2)ratio
FROM t1 GROUP BY user_grade ORDER BY ratio DESC;