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

with t as (
        select uid,max(date_format(in_time,"%Y-%***x_date,min(date_format(in_time,"%Y-%m-%d")) min_date
        from tb_user_log
        group by uid
)
select "忠实用户" user_grade,round(sum(if(datediff("2021-11-04",max_date)<7&&datediff("2021-11-04",min_date)>=7,1,0))/count(uid),2) ratio from t
union
select "新晋用户" user_grade,round(sum(if(datediff("2021-11-04",max_date)<7&&datediff("2021-11-04",min_date)<7,1,0))/count(uid),2) ratio from t
union
select "沉睡用户" user_grade,round(sum(if(datediff("2021-11-04",max_date)>=7&&datediff("2021-11-04",max_date)<30,1,0))/count(uid),2) ratio from t
union
select "流失用户" user_grade,round(sum(if(datediff("2021-11-04",max_date)>=30,1,0))/count(uid),2) ratio from t