题解 | #各用户活跃分层人数统计#

with t1 as (select
    uid,
    timestampdiff (
        day,
        login_date,
        (
            select
                max(login_date)
            from
                user_login_tb
        )
    ) as jiange
from
    user_login_tb)
select user_grade,count(uid) as num,round(count(uid)/sum1,2) as ratio from (select *,(select count(distinct uid) from t1) as sum1 from (select uid,(case when min1>=30 then '流失用户' when min1<30 and min1>7 then '沉默用户' when min1=0 and min2>30 then '回流用户' when max1<=7 then '新增用户' else '忠实用户' end) as user_grade from (select t1.uid,max(jiange) as max1,min(jiange) as min1,min2 from t1 left join (select uid,min(jiange) as min2 from (select t1.uid,jiange,min1 from t1 left join (select uid,min(jiange) as min1 from t1 group by uid) t2 on t1.uid=t2.uid ) t3 where jiange>min1 group by uid 
) t4 on t1.uid=t4.uid group by uid) tf) k) k1 group by user_grade order by num desc