题解 | #统计活跃间隔对用户分级结果#
统计活跃间隔对用户分级结果
https://www.nowcoder.com/practice/6765b4a4f260455bae513a60b6eed0af
with t1 as( # 1. 新晋用户(近7天新增),用户注册表 select uid, min(date_format(in_time,"%Y-%m-%d")) as login from tb_user_log group by uid having datediff((select max(date_format(out_time,"%Y-%m-%d")) from tb_user_log),login) < 7 ), t2 as( # 2. 忠实用户(近7天活跃过且非新晋用户),用户在线表,剔除t1 select * from( (select uid, date_format(in_time,"%Y-%m-%d") as dt from tb_user_log) union (select uid, date_format(out_time,"%Y-%m-%d") as dt from tb_user_log) ) t where datediff((select max(date_format(out_time,"%Y-%m-%d")) from tb_user_log),dt) < 7 and uid not in (select uid from t1) ), t3 as( # 3. 沉睡用户(近7天未活跃但更早前活跃过) select * from( (select uid, date_format(in_time,"%Y-%m-%d") as dt from tb_user_log) union (select uid, date_format(out_time,"%Y-%m-%d") as dt from tb_user_log) ) t where datediff((select max(date_format(out_time,"%Y-%m-%d")) from tb_user_log),dt) >= 7 and datediff((select max(date_format(out_time,"%Y-%m-%d")) from tb_user_log),dt) <30 and uid not in (select uid from t2) ), t4 as( # 4. 流失用户(近30天未活跃但更早前活跃过) select * from( (select uid, date_format(in_time,"%Y-%m-%d") as dt from tb_user_log) union (select uid, date_format(out_time,"%Y-%m-%d") as dt from tb_user_log) ) t where datediff((select max(date_format(out_time,"%Y-%m-%d")) from tb_user_log),dt) >= 30 and uid not in (select uid from t2) and uid not in (select uid from t3) ) (select '新晋用户' as user_grade, round(count(distinct uid)/(select count(distinct uid) from tb_user_log),2) as ratio from t1) union (select '忠实用户' as user_grade, round(count(distinct uid)/(select count(distinct uid) from tb_user_log),2) as ratio from t2) union (select '沉睡用户' as user_grade, round(count(distinct uid)/(select count(distinct uid) from tb_user_log),2) as ratio from t3) union (select '流失用户' as user_grade, round(count(distinct uid)/(select count(distinct uid) from tb_user_log),2) as ratio from t4) order by ratio desc
select user_grade, round(count(uid)/(select count(distinct uid) from tb_user_log),2) as ratio from( # 2. 查询最近一次登陆时间与max_time之差,注册时间与max_time之差来分组 select uid, case when datediff((select max(in_time) from tb_user_log),last_time)<7 and datediff((select max(in_time) from tb_user_log),first_time)<7 then '新晋用户' when datediff((select max(in_time) from tb_user_log),last_time)<7 and datediff((select max(in_time) from tb_user_log),first_time)>=7 then '忠实用户' when datediff((select max(in_time) from tb_user_log),last_time)>=7 and datediff((select max(in_time) from tb_user_log),last_time)<30 then '沉睡用户' else '流失用户' end as user_grade from ( # 1. 查询每个用户最近一次的登陆时间和用户注册时间 select uid, date_format(max(in_time),'%Y-%m-%d') as last_time, date_format(min(in_time),'%Y-%m-%d') as first_time from tb_user_log group by uid ) t ) t1 group by user_grade order by ratio desc