题解 | #统计活跃间隔对用户分级结果#
统计活跃间隔对用户分级结果
https://www.nowcoder.com/practice/6765b4a4f260455bae513a60b6eed0af
select user_grade, round(count(user_grade)/(select count(distinct uid) from tb_user_log),2) ratio
from
(select
case when f_time=l_time and diff < 7 then '新晋用户'
when f_time!=l_time and diff < 7 then '忠实用户'
when diff >=7 and diff <30 then '沉睡用户'
when diff >=30 then '流失用户'
end 'user_grade'
from
(select uid,min(date(in_time)) f_time, max(date(in_time)) l_time, timestampdiff(day, max(date(in_time)), date('2021-11-04')) diff
from tb_user_log
group by uid) a -- 最后一次登陆)
) b
group by user_grade
order by ratio desc
俺好累
