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

select user_grade,round(count(*)/total_num,2) as ratio
from
(
    select uid,total_num,
    case 
    when datediff(today,min(in_day))>6 and datediff(today,max(in_day))<6 then '忠实用户'
    when datediff(today,min(in_day))<6 then '新晋用户'
    when datediff(today,max(in_day))between 7 and 29 then '沉睡用户'
    else '流失用户' end as user_grade
    from
    (select tag,max(date(in_time)) as today,count(distinct uid) as total_num
    from (
    select *, 1 as tag
    from tb_user_log
    ) as t1
    group by tag) as t2
    right join
        (select uid,date(in_time) as in_day, 1 as tag 
        from tb_user_log) as t3 using(tag)
    group by uid,total_num
) as t4
group by user_grade
order by ratio desc;