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

select user_grade,
round(count(user_grade)/(select count(distinct uid) from tb_user_log),2) as ratio
from
    (select uid,
    case 
    when min(day_t)<7 and max(day_t)>=7 then '忠实用户'
    when min(day_t)>0 and max(day_t)<7 then '新晋用户'
    when min(day_t)>=7 and min(day_t)<30 then '沉睡用户'
    when min(day_t)>=30 then '流失用户'
    else null
    end as user_grade
    from
        (select uid,
        datediff(cur_date,date(out_time)) as day_t
        from tb_user_log
        left join
        (select date(max(out_time)) as cur_date from tb_user_log) as t1 
        on 1
         )as t2
    group by uid
     )as t3
group by user_grade
order by ratio desc