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

with T1_table as ( 
select "忠实用户" as user_grade, round(T1_num/(T1_num+T2_num+T3_num+T4_num),2) as "ratio"
from (

select sum(case when (datediff((select max(in_time) from tb_user_log), min_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<=6) then 1 else 0 end) as "T1_num",sum(case when (datediff((select max(in_time) from tb_user_log), min_time)<=6) then 1 else 0 end) as "T2_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<30 then 1 else 0 end) as "T3_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>=30 then 1 else 0 end) as "T4_num"
from(
select uid, min(in_time) as "min_time",max(in_time) as "max_time"
from tb_user_log
group by uid
)t1
)t2
), T2_table as (
select "新晋用户" as user_grade, round(T2_num/(T1_num+T2_num+T3_num+T4_num),2) as "ratio"
from (

select sum(case when (datediff((select max(in_time) from tb_user_log), min_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<=6) then 1 else 0 end) as "T1_num",sum(case when (datediff((select max(in_time) from tb_user_log), min_time)<=6) then 1 else 0 end) as "T2_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<30 then 1 else 0 end) as "T3_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>=30 then 1 else 0 end) as "T4_num"
from(
select uid, min(in_time) as "min_time",max(in_time) as "max_time"
from tb_user_log
group by uid
)t1
)t2
), T3_table as (
select "沉睡用户" as user_grade, round(T3_num/(T1_num+T2_num+T3_num+T4_num),2) as "ratio"
from (

select sum(case when (datediff((select max(in_time) from tb_user_log), min_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<=6) then 1 else 0 end) as "T1_num",sum(case when (datediff((select max(in_time) from tb_user_log), min_time)<=6) then 1 else 0 end) as "T2_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<30 then 1 else 0 end) as "T3_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>=30 then 1 else 0 end) as "T4_num"
from(
select uid, min(in_time) as "min_time",max(in_time) as "max_time"
from tb_user_log
group by uid
)t1
)t2
), T4_table as (
select "流失用户" as user_grade, round(T4_num/(T1_num+T2_num+T3_num+T4_num),2) as "ratio"
from (

select sum(case when (datediff((select max(in_time) from tb_user_log), min_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<=6) then 1 else 0 end) as "T1_num",sum(case when (datediff((select max(in_time) from tb_user_log), min_time)<=6) then 1 else 0 end) as "T2_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>6 and datediff((select max(in_time) from tb_user_log), max_time)<30 then 1 else 0 end) as "T3_num", sum(case when datediff((select max(in_time) from tb_user_log), max_time)>=30 then 1 else 0 end) as "T4_num"
from(
select uid, min(in_time) as "min_time",max(in_time) as "max_time"
from tb_user_log
group by uid
)t1
)t2
)

select * from T1_table
union all
select * from T2_table
union all
select * from T3_table
union all
select * from T4_table
order by ratio desc