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

select n1, round(n2/ t1, 2) as ratio from ( select case when timestampdiff(day, date(t1.d1), '2021-11-04') <= 6 then '新晋用户' when timestampdiff(day, date(t2.d1), '2021-11-04') >= 29 then '流失用户' when timestampdiff(day, date(t1.d1), '2021-11-04') > 6 and timestampdiff(day, date(t2.d1), '2021-11-04') <= 6 then '忠实用户' else '沉睡用户' end as n1, count(*) as n2 from (select uid, min(date(in_time)) as d1 from tb_user_log group by uid)t1 left join (select uid, max(date(in_time)) as d1 from tb_user_log group by uid)t2 using (uid) group by n1 )t3 left join ( select count(distinct uid) as t1 from tb_user_log )t4 on 1 order by ratio desc