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

with data as (
select
    uid,
    min(in_time) time,
    max(out_time) last_login
from
    tb_user_log
group by uid
),
data2 as (
select
*,
case
when timestampdiff(day,time,(select max(out_time) from tb_user_log ))<7 then "新晋用户"
when timestampdiff(day,last_login,(select max(out_time) from tb_user_log ))<7 then '忠实用户'
when timestampdiff(day,last_login,(select max(out_time) from tb_user_log ))<30 then '沉睡用户'
else '流失用户'
end as user_grade
from
data
)
select
user_grade,
round(count(*)/( select count(*) from data   ) ,2)as ratio
from data2
group by user_grade
order by 
ratio desc