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

with merge_user_tb as (
    SELECT DISTINCT
				uid,
				FIRST_VALUE(dt) over(PARTITION by uid ORDER BY dt)      as start_day,
			  FIRST_VALUE(dt) over(PARTITION by uid ORDER BY dt desc) as end_day,
			  max(dt) over()                                          as today	
    FROM(
    SELECT DATE_FORMAT(in_time,'%Y-%m-%d') dt,uid FROM tb_user_log
       UNION
    SELECT DATE_FORMAT(out_time,'%Y-%m-%d') dt,uid FROM tb_user_log
    )t_1
),
caculate_user_tb as (
SELECT *,case when DATEDIFF(today,start_day) >= 7 and DATEDIFF(today,end_day) < 7   then '忠实用户'
            when DATEDIFF(today,start_day) < 7 and DATEDIFF(today,end_day) < 7  then '新晋用户'
			      when DATEDIFF(today,start_day) >= 7   and DATEDIFF(today,end_day) >= 7 and DATEDIFF(today,end_day) < 30  then '沉睡用户'
			      when DATEDIFF(today,start_day) >= 30 and  DATEDIFF(today,start_day)>=30 then '流失用户'
			  else null end as user_grade,
				DATEDIFF(today,start_day),DATEDIFF(today,end_day)
FROM merge_user_tb)
SELECT 
   user_grade,round(count(*)/(select count(*) FROM caculate_user_tb),2) as ratio
FROM caculate_user_tb 
GROUP BY user_grade
ORDER BY ratio desc,user_grade;

临界值不好把控