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

with parameters as(
    select
        date(max(out_time)) cur_date,
        date_sub(date(max(out_time)),interval 6 day) cur_7,
        date_sub(date(max(out_time)),interval 29 day) cur_30
    from
        tb_user_log
)
,user_detail as(
    select
        uid,
        min(in_time) first_open,
        max(out_time) last_open,
        case when min(in_time)< (select cur_7 from parameters) and max(out_time) >= (select cur_7 from parameters) then  "忠实用户" 
             when min(in_time)>= (select cur_7 from parameters)  then "新晋用户" 
             when max(out_time) >= (select cur_30 from parameters) and max(out_time) < (select cur_7 from parameters) then "沉睡用户"
             when  max(out_time) < (select cur_30 from parameters) then "流失用户"
        end user_grade
    from
        tb_user_log
    group by 
        1
)
select user_grade,
        round(count(1)/sum(count(*)) over(),2) ratio
from user_detail
group by 1 
order by 2 desc

eezz