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

# 字段：user_grade 和 ratio
# 忠实用户：近7天活跃过且非新晋用户、新晋用户(近7天新增)、
# 沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)

# tb1：筛选最近的时间点/uid
with tb1 as(
    select uid, max(date(in_time)) as day1, min(date(in_time)) as day2
    from  tb_user_log
    group by uid
),
# tb2：选出最大时间
tb2 as(
    select max(day1) as max_day
    from tb1
),
# tb3：时间差 // 判断是否新用户
# 新用户定义：今天内新增（最早时间点day2与最近时间点day1的时间差小于等于7）
tb3 as(
    select uid,
    TIMESTAMPDIFF(DAY, day1, max_day) as day_diff,day1,day2,
    # 判断是否新用户
    if(timestampdiff(day,day2,day1)<=7,1,0) as if_new
    from tb1 join tb2 
),
# tb4：定义用户分层(根据最近登录时间day_diff 和 是否是新用户if_new)
# 忠实用户：近七天活跃day_diff<=6 且 非新用户if_new = 0
# 新晋用户：近七天活跃day_diff<=6 且 新用户if_new = 1
# 沉睡用户：近七天未活跃day_diff>=6 且 三十天内活跃 day_diff<=29
# 流失用户：三十天内未活跃day_diff > 29
tb4 as(
select uid,
    case 
    when day_diff<=6 and if_new = 0 then '忠实用户'
    when day_diff<=6 and if_new = 1 then '新晋用户'
    when day_diff>=6 and day_diff<=29 then '沉睡用户'
    when day_diff > 29 then '流失用户' end as user_grade
    from tb3
)
# 查询结果
select user_grade,
round(count(uid)/(select count(distinct uid) from tb4),2) as ratio
from tb4
group by user_grade
order by ratio desc