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

明确题意：

统计活跃间隔对用户分级后，各活跃等级用户占比

问题分解：

按用户分组：group by uid

计算用户注册时间：date(min(in_time))

计算用户最近活跃时间：date(max(out_time))

计算用户总数：count(distinct uid)

对用户分级：case when timestampdiff(day,active_dt,cur_dt) >= 30 then '流失用户' when timestampdiff(day,active_dt,cur_dt) >= 7 then '沉睡用户' when timestampdiff(day,register_dt,cur_dt) < 7 then '新晋用户' else '忠实用户' end

按活跃等级分组：group by user_grade,user_cnt

计算各活跃等级用户占比：count(uid) / user_cnt

细节问题：

保留两位小数：round(x,2)

按占比降序排序：order by ratio desc

select
    user_grade,
    round(count(uid) / user_cnt, 2) ratio
from(
select
    uid,
    user_cnt,
    case
    when timestampdiff(day,active_dt,cur_dt) >= 30 then '流失用户'
    when timestampdiff(day,active_dt,cur_dt) >= 7 then '沉睡用户'
    when timestampdiff(day,register_dt,cur_dt) < 7 then '新晋用户'
    else '忠实用户'
    end user_grade
from(
select 
    uid,
    date(min(in_time)) register_dt,
    date(max(out_time)) active_dt
from tb_user_log
group by uid
) t1
left join 
(
select
    date(max(out_time)) cur_dt,
    count(distinct uid) user_cnt
from tb_user_log
) t2 on 1 # 这里也可以不join，直接在select里嵌套查这两个字段
) t3
group by user_grade,user_cnt
order by ratio desc;