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

#新晋
with xinjin as(
 select uid,min(date(in_time)) as first_date
 from tb_user_log
 group by uid
  having first_date>=date_sub( (select max(date(in_time)) as today  from tb_user_log),interval 6 day)
 and first_date<=(select max(date(in_time)) as today  from tb_user_log)),

#忠实
zhongshi as(
select distinct t.uid
from tb_user_log t
where date(t.in_time)>=date_sub( (select max(date(in_time)) as today  from tb_user_log),interval 6 day)
and date(t.in_time)<=(select max(date(in_time)) as today  from tb_user_log)
and t.uid not in(select uid from xinjin)),

#流失
liushi as(
select uid
from tb_user_log
where date(in_time)<date_sub( (select max(date(in_time)) as today  from tb_user_log),interval 29 day)
and uid not in(select uid from zhongshi)
and uid not in(select uid from xinjin)),

#沉睡
chenshui as(
select uid
from tb_user_log
where date(in_time)<date_sub( (select max(date(in_time)) as today  from tb_user_log),interval 6 day)
and uid not in(select uid from zhongshi)
and uid not in(select uid from xinjin)
and uid not in(select uid from liushi))

#计算
select user_type,
round(count(distinct a.uid)/total_users,2) ratio
from(
select t.uid,
    case
        when xj.uid is not null then '新晋用户'
        when zs.uid is not null then '忠实用户'
        when ls.uid is not null then '流失用户'
        when cs.uid is not null then '沉睡用户'
        else '其他' 
    end as user_type
from  (
        select distinct uid  -- 关键去重：确保每个用户只计算一次
        from tb_user_log
    ) t
left join xinjin xj on t.uid = xj.uid
left join zhongshi zs on t.uid = zs.uid
left join liushi ls on t.uid = ls.uid
left join chenshui cs on t.uid = cs.uid)a

cross join (
    select count(distinct uid) as total_users  -- 计算总用户数
    from tb_user_log) b
group by user_type, total_users
order by ratio desc

超绝复杂方法