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

with judge_table as(
select 
uid,
TIMESTAMPDIFF(day,early_time,(select max(out_time) from tb_user_log)),
TIMESTAMPDIFF(day,lately_time,(select max(out_time) from tb_user_log)),
if(
TIMESTAMPDIFF(day,early_time,(select max(out_time) from tb_user_log))  > 6
 and TIMESTAMPDIFF(day,lately_time,(select max(out_time) from tb_user_log)) < 7,1,0) as zhongshi,

if(TIMESTAMPDIFF(day,early_time,(select max(out_time) from tb_user_log)) < 7
and  TIMESTAMPDIFF(day,lately_time,(select max(out_time) from tb_user_log)) < 7,1,0) as xinjin,

if(TIMESTAMPDIFF(day,lately_time,(select max(out_time) from tb_user_log)) > 6
and TIMESTAMPDIFF(day,lately_time,(select max(out_time) from tb_user_log)) < 29,1,0) as chenshui,

if(TIMESTAMPDIFF(day,lately_time,(select max(out_time) from tb_user_log)) > 29,1,0) as liushi

from
(
select uid,
min(date(in_time)) as early_time,
max(date(in_time)) as lately_time
from tb_user_log
group by  uid) t1)

select 
"忠实用户",
round(sum(zhongshi) / count(*),2)
from judge_table
union
select 
"新晋用户",
round(sum(xinjin) / count(*),2)
from judge_table
union
select 
"沉睡用户",
round(sum(chenshui) / count(*),2)
from judge_table
union
select 
"流失用户",
round(sum(liushi) / count(*),2)
from judge_table

其实只需要拆开为两个日期：一个是注册到今天的日期，以及最晚活跃时间到今天的日期，结合这两个日期与7、30的关系就能得到用户的判断类型。思路并不难，只是想想写一个完美的代码需要点时间