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

select user_grade,round(count(uid)/max(user_cnt),2)
from
    (select uid,user_cnt
    ,case
    when last_time_diff >= 30 then '流失用户'
    when last_time_diff >= 7 then '沉睡用户'
    when first_time_diff <= 7 then '新晋用户'
    else '忠实用户'
    end user_grade
    from
        (select uid,user_cnt
        ,datediff(max_time,first_time) first_time_diff
        ,datediff(max_time,last_time) last_time_diff
        from
            (select uid,min(date(in_time)) first_time,max(date(out_time)) last_time
            from tb_user_log
            group by uid
            )t1
            left join 
            (select max(date(out_time)) max_time,count(distinct uid) user_cnt from tb_user_log
            )t2
        on 1
        )t3
    )t4
group by user_grade
order by 2 desc